Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-04-25
5
Medium Priority
?
2,805 Views
Last Modified: 2012-05-05
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
Comment
Question by:milkmon123
  • 2
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
butterhook earned 2000 total points
ID: 16532915
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
 
LVL 1

Author Comment

by:milkmon123
ID: 16533246
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
 
LVL 1

Author Comment

by:milkmon123
ID: 16533285
Man what a dufus !!!!

You were right !!! - Sorry about that.

Max points to butterhook - thanks man.

Matt
0
 
LVL 1

Expert Comment

by:butterhook
ID: 16533636
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
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24617771
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month21 days, 5 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question