English / American date formatting using VBA

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.
LVL 6
CaptainGibletsAsked:
Who is Participating?
 
ragoranConnect With a Mentor Commented:
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") & "#"
0
 
Rey Obrero (Capricorn1)Commented:
try

and [Date recorded] BETWEEN #" & format(txtdate1,'dd/mm/yyyy') & "# AND #" & Format(txtdate2,'dd/mm/yyyy') & "#"
0
 
Rey Obrero (Capricorn1)Commented:
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))
0
 
tbsgadiCommented:
Hi CaptainGiblets,

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

Good Luck!

Gary
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Mr. Ragoran is on the right track. Read on here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22874125.html#20024077

/gustav
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.

All Courses

From novice to tech pro — start learning today.