sql syntax problem

[raynebair]

I am having problems getting my sql syntax correct. This is what I have and it doesn't work:

SELECT id, name as AName FROM artists WHERE Mid(name, 1, 1) Not Between Asc('A') And Asc('z') ORDER BY name

There error I get is Expected End of Statement

I need to return all records where name doesn't start with a letter. This sql statement is querying an Access database via asp.

Can anyone tell me how it should be written?

Thanks!

[-TheDarkEye-]

try this:

SELECT id, name AS AName
FROM artists
WHERE name BETWEEN 'A%' AND 'Z%'
ORDER BY name

if case sensitivity if a prob youll have to do something like this:

SELECT id, name AS AName
FROM artists
WHERE (name BETWEEN 'A%' AND 'Z%')
OR (name BETWEEN 'a%' and 'z%')
ORDER BY name

let me know if this works.

[goober]

DarkEye,

the code that you supplied seems like it will return all the records that START with a letter. Will it?

Couldn't you do "WHERE Name NOT BETWEEN 'A%' AND 'Z%'" ?

Just a suggestion, seems like in theory it should work.

'Till next time..

[shane]

Try the following.

SELECT 	ID,
	Name As [AName]
FROM 	Artists
WHERE 	LEFT(Name,1) Like '[!a-z]'
ORDER BY Name;

[-TheDarkEye-]

ACK! Thx for catching that.

[goober]

Anytime. Just caught my eye.

[raynebair]

Thanks Shane. That worked but I modified it to use Mid(Name,1,1). I thought with Left you specified what position to start with and it would go to the end from there. With Mid I specify where to start and how many characters I want.

The not in A-z worked great!