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

Posted on 2006-04-25
Last Modified: 2012-05-05

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

    Accepted 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.
    LVL 1

    Author Comment

    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 ?
    LVL 1

    Author Comment

    Man what a dufus !!!!

    You were right !!! - Sorry about that.

    Max points to butterhook - thanks man.

    LVL 1

    Expert Comment

    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.

    LVL 30

    Expert Comment

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



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Share codes 68 104
    MS Access "DoCmd.applyfilter" 16 72
    LINQ return type for nested group query 6 51
    Input past end of file vbs script 9 58
    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now