English / American date formatting using VBA

Posted on 2007-10-16
Last Modified: 2013-11-28
i have this code

                            sqladvanced = sqladvanced & " where live.[SalesPerson] = '" & cborepsearch.Value & "' and live.[" & cbocriteria1 & "] = '" & txtcriteria1.Value & "'" & " and live.[" & cbocriteria2 & "] = '" & txtcriteria2.Value & "'" & " And live.[" & cbocriteria3 & "] = '" & txtcriteria3.Value & "' and [Date recorded] BETWEEN #" & txtdate1 & "# AND #" & txtdate2 & "#"

and when i do a search it looks for an american date

the txtdate1 and txtdate2 are the microsoft date pickers, they are set up as english format, as is the format of the date in the database.

Can any one tell me a way to get it to search as english formatting please.
Question by:CaptainGiblets
    LVL 119

    Expert Comment

    by:Rey Obrero

    and [Date recorded] BETWEEN #" & format(txtdate1,'dd/mm/yyyy') & "# AND #" & Format(txtdate2,'dd/mm/yyyy') & "#"
    LVL 119

    Expert Comment

    by:Rey Obrero
    or you can use the dateserial function

    and [Date recorded] BETWEEN dateserial(year(txtdate1),month(txtdate1),day(txtdate1))  AND dateserial(year(txtdate2),month(txtdate2),day(txtdate2))
    LVL 14

    Accepted Solution

    Date value are not store formatted.  The format is only for display.

    However, as you are building a string and you have not specify a format, VBA will convert the date value in the textbox to the american format...

    What I do is this to prevent any ambiguity:

    [Date recorded] BETWEEN #" & format(txtdate1,"YYYY-MM-DD") & "# AND #" & format(txtdate2,"YYYY-MM-DD") & "#"
    LVL 46

    Expert Comment

    Hi CaptainGiblets,

    Change all dates in SQL for Format(DateField,"mm/dd/yy")- SQL understands in american!

    Good Luck!

    LVL 48

    Assisted Solution

    by:Gustav Brock
    Mr. Ragoran is on the right track. Read on here:


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    MS Access Search and Replace Using VBA 6 29
    calculated field in a table? 3 20
    Order by 8 21
    Custom auto number 6 29
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    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

    14 Experts available now in Live!

    Get 1:1 Help Now