Access to release date - dd / mm automatically changed to mm / dd

Question

I encoded something using a Date statement in Access VBA. It worked fine until the beginning of this month, but now I see that the date automatically changed the format from dd/mm/yyyy to mm/dd/yyyy . Has anyone else encountered the same problem?

Answer 1

The default date format for Access SQL, regardless of locale, is mm / dd / yyyy. If you use an invalid date format, it will “help” try to convert this to a valid date for you.

So, if you use "09/30/2008", it will know that you are using dd / mm / yyyy and convert it accordingly. However, a value like "10/01/2008" is a valid mm / dd / yyyy value for starters, so it will not be converted or stored incorrectly if you really meant dd / mm / yyyy ....

The solution is to always convert your date values ​​to the string mm / dd / yyyy before using them in Access SQL operations. You should be a little careful here, since using VBA date format masks may not work fully, as you would expect on non-US locales (for example, it is “useful” to interpret “mm / dd / yyyy” as “localized short date format,”) therefore, please consult your specific version of Access / VBA carefully.

Answer 2

Access requires a unique date. It is generally recommended to use yyyy / mm / dd, regardless of language. For instance:

 strSQL="SELECT SomeDate FROM tblT WHERE SomeDate=#" & Format(DateVar, "yyyy/mm/dd") & "#" 

Answer 3

Try this code:

 stLinkCriteria = "[ProjectDate] Between #" & Format(CDate(Me![txtDateFrom]), "mm/dd/yyyy") & "# And #" & Format(CDate(Me![txtDateTo]), "mm/dd/yyyy") & "#" 

This works for me.

Answer 4

I have very successfully used the datevalue () function. When getting dates from unrelated controls, it seems smart enough to correctly interpret the "dd / mm / yyyy" format. So a Jet SQL query like

"Select * from DateTable where StartDate = datevalue(" & me!TxtStartDate & ");"

It seems that

works every time.

Answer 5

It works:

 sentenciaSQL = "UPDATE Numeraciones " & _ "SET Valor = " & Valor & ", " & _ "Fecha = #" & **Format(fecha,"mm/dd/yyyy HH:nn:ss") & "#, " &** _ "Id_Usuario = " & Id_Usuario & _ " WHERE Nombre = '" & Nombre & "'"