By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

access sql ignores "-"

P: 1

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?


Dec 26 '06 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,418
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!
Dec 26 '06 #2

Expert Mod 15k+
P: 31,602
PEB is exactly right.
Try changing the quotes for parentheses :
(420-1) and (420-5) rather than '420-1' and '420-5'.
Dec 30 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.