• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2865
  • Last Modified:

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


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)



Urgent so Max points
  • 2
  • 2
1 Solution
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.
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.

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.

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


 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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now