Link to home
Start Free TrialLog in
Avatar of CaptainGiblets
CaptainGibletsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try

and [Date recorded] BETWEEN #" & format(txtdate1,'dd/mm/yyyy') & "# AND #" & Format(txtdate2,'dd/mm/yyyy') & "#"
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))
ASKER CERTIFIED SOLUTION
Avatar of ragoran
ragoran
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi CaptainGiblets,

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

Good Luck!

Gary
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial