CaptainGiblets
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.
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.
or you can use the dateserial function
and [Date recorded] BETWEEN dateserial(year(txtdate1), month(txtd ate1),day( txtdate1)) AND dateserial(year(txtdate2), month(txtd ate2),day( txtdate2))
and [Date recorded] BETWEEN dateserial(year(txtdate1),
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi CaptainGiblets,
Change all dates in SQL for Format(DateField,"mm/dd/yy ")- SQL understands in american!
Good Luck!
Gary
Change all dates in SQL for Format(DateField,"mm/dd/yy
Good Luck!
Gary
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and [Date recorded] BETWEEN #" & format(txtdate1,'dd/mm/yyy