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

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
0
milkmon123
Asked:
milkmon123
  • 2
  • 2
1 Solution
 
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.
0
 
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 ?
0
 
milkmon123Author Commented:
Man what a dufus !!!!

You were right !!! - Sorry about that.

Max points to butterhook - thanks man.

Matt
0
 
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.

0
 
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

0
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