finding records beginning with single quote 1

[stinkybee]

I am trying to implement a standard browse feature on a web page where the user can select a to z or miscellaneous. All records with where the "title" field begins with that letter are then displayed. My problem is that I have noticed that when trying to display the "Miscellaneous" records, as in, the ones that do not begin with a letter, a record that has the "title" field beginning with a single quote ( ' ) is not displayed.

I am using a simple sql statement as follows

SELECT * FROM test WHERE title NOT BETWEEN 'A' AND 'Z'

Other characters and numbers etc work fine, just not the single quote. Also, if I search for it explicitly it works fine. eg

SELECT * FROM test WHERE title = '''test'

Any help on this would be gratefully received.

 

[Remou]

How about a work-around?

SELECT t.Title, *
FROM t
WHERE Replace([Title],"'","1") Not Between "A" And "Z"

 

[PHV]

What about this ?
SELECT * FROM test WHERE NOT (title LIKE '[A-Z]*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Great! Thanks.

 

[PHV]

OOps, sorry for the missing closing paren.

 

[stinkybee]

Thanks for the reply. Had to make a slight change but this works

SELECT * FROM test WHERE NOT (title LIKE '[A-Z]%')