[Last Call] Learn how to a build a cloud-first strategyRegister Now


English / American date formatting using VBA

Posted on 2007-10-16
Medium Priority
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20086128

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20086147
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

ragoran earned 500 total points
ID: 20086150
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

ID: 20086152
Hi CaptainGiblets,

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

Good Luck!

LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 20087309
Mr. Ragoran is on the right track. Read on here:



Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

825 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