BETWEEN search on text fields question

[JohnJohn]

Hi. I'm having a very hard time finding any information on the BETWEEN keyword when using SQL syntax in Access. It may seem like a weird search to you, but here's what I need to do:

1) The value stored in Field1 in a particular record looks like this:
field1 = '- ' (that's a dash with 11 spaces)

2) Here's the query I'm running:
SELECT * FROM MyTable WHERE Field1 BETWEEN ',,,,,,,,,,,,' AND 'zzzzzzzzzzzz'

This returns 0 records, but I want to structure it to return the values from step 1. I'm sure there are special rules for text comparisons in Access, but doggonnit if I can't find 'em. This query returns the appropriate records in SQL Server.

Thanks for ANY help!

John

 

[John Spencer (MVP)]

Normally Access will strip trailing spaces, so I would expect that if you are
using Access and JET that all that is stored is "-". I would try:

Field1 Between "-" And "zzzzzzzzzzzz"