VB6 / MS Access - Date Query - List items between two dates...

Hi

I cannot get this query to work...

SELECT * FROM [M:\Midweb\Crf\orders.mdb].[order] WHERE (o_deleted = 'Y' OR o_cancelled = 'Y') AND (o_date BETWEEN #18/04/2006# AND #25/04/2006#) ORDER BY o_ref

It' lists ALL items, not just between the two dates.

Any ideas ?

System is set to UK date format (DD/MM/YYYY)

Thanks

Matt

P.S.
Urgent so Max points
LVL 1
milkmon123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

butterhookCommented:
Try turning round the date query string i.e. use BETWEEN #04/18/2006# AND #04/25/2006#

I have had proper nightmares with Access formatting dates oddly, I am in the UK as well.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
milkmon123Author Commented:
Hi there.

No it isn't that - it's working now (sort of) - the format is correct DD/MM/YY etc - but to display everything on 20/01/2006 - you have to have between 19/01/2006 and 21/01/2006.

Is there a reason for this ?

I know I can program around it, by taking one day off the date from and adding one onto the date to, but this is a bit of a cop-out.

P.S.
Like your name - is that from the Roger Moore line from Live and Let Die ?
milkmon123Author Commented:
Man what a dufus !!!!

You were right !!! - Sorry about that.

Max points to butterhook - thanks man.

Matt
butterhookCommented:
Mate, for finding entries for a specific date you can just do

 'WHERE o_date = #WHATEVERDATE#'

 rather than specifying the range. If this doesn't work or if you have hours,minutes, seconds in your stored dates then you may be able to do

 'WHERE o_date >= #DAYBEFORE# AND o_date <= #DAY AFTER#'

Yes, it is from the Bond film.

Wayne BarronAuthor, Web DeveloperCommented:
Thank you butterhook:
You information here helped me get a Query running that I was using against a Text field
That I had to change to work against a date/time field.

I was doing as such to begin with, which worked for the text field (Without the # signs for Text)
WHERE (((Members.MyBirth)>#10/16/1993# And (Members.MyBirth)<#2/18/1970#));

So, as you can see, I have changed it to this, and works like an absolute charm:
WHERE (((Members.MyBirth)>=#2/18/1970# And (Members.MyBirth)<=#10/16/1993#));

Thanks once again.
Keep up the awesome work.

Carrzkiss

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.