Problem in searching data

Dear All,

Hope all in good tune.

I am facing a problem. I want to search the records between two dates. I am using the command as:

SELECT *
FROM mybilldet
WHERE customer='(6) SINHA AUTO DISTUBUTOR' and billdate between (01/07/2006 and 31/07/2006)
ORDER BY billdate;

also I have used

SELECT *
FROM mybilldet
WHERE customer='(6) SINHA AUTO DISTUBUTOR' and billdate >=#01/07/2006# and billdate<=#31/07/2006#
ORDER BY billdate;

The billdate field is a date type field. I am using an Access database.

But the above two commands are not working. The records are there in the database from 18/07/2006. If I use

SELECT *
FROM mybilldet
WHERE customer='(6) SINHA AUTO DISTUBUTOR' and billdate >=#18/07/2006# and billdate<=#31/07/2006#
ORDER BY billdate;

then it is working. Can anybody help me out.

Thanks in advance

you must use US date format

VB Code:

WHERE customer='(6) SINHA AUTO DISTUBUTOR' and billdate >=# format(01/07/2006, "mm/dd/yyyy") # and billdate<=# format(31/07/2006, "mm/dd/yyyy") #

access requires date in that order

u can also try with this

VB Code:

SELECT *
FROM mybilldet
WHERE customer='(6) SINHA AUTO DISTUBUTOR' 
and billdate between '01/07/2006' and '31/07/2006'
ORDER BY billdate;

Thanks SHABA. It worked fine. Thank you very very much because I was fool in applying braces in between operator.

plz mark it as resolved if ur problem has been solved :)

Thanks NOSHABA. It worked fine. Thank you very very much because I was fool in applying braces in between operator.