access sql ignores "-"

[mati]

shalom

I've sent the following sql query:

SELECT CL_NUMBER From MEMBERS where CL_NUMBER>='420-1' and CL_NUMBER<='420-5'

As a result I received: 420-1 4201-1 420-5

It seems that the access qsl I'm using , ignores the "-" character.

What do I do to prevent it?

Thanks

Mati

[PEB]

Hi Mati,

Marry Cristmas!

In fact you are doing string comparison here in your SQL! So using this kind of comparison Access arranges or evaluates >= or <= using the ASC value of the respective character!

I think that isn't usefull to do this kind of comparision here!

If u want to get the specified numbers... The best is to convert the first part in number and the second one in number...

This conversion can be done using those expressions:

for the first part:

FpartNumber: left([My_number],instr(1,[My_number],"-")-1)

for the second part:

SpartNumber: right([My_number],len([My_number])-instr(1,[My_number],"-")+1)

When u have thos numbers u can apply > < and the others arithmetic operators for comparision!

Hope this helps!

[NeoPa]

PEB is exactly right.
Try changing the quotes for parentheses :
(420-1) and (420-5) rather than '420-1' and '420-5'.