If you write even simple VBA code, or use forms to set dates to run reports or queries, at some point you will probably have been tripped up by international date formats in Access or Excel, particularly if you are in the UK or somewhere that doesn’t use the American date format.
Internally Access treats all dates in the American format – mm/dd/yyyy however when you type dates into forms or queries Access is clever enough to interpret your localised date format. This can go wrong though. In the UK If you were to enter 2/13/20 by mistake, Access would accept it as a valid date of 13/02/2020! We know that there isn’t a month 13 but Access by trying to be helpful makes things worse.
In the UK if you were to use some criteria in a query in a date field like 1/12/2019 it will format it like this:
In the UK this is 1st December 2019, however Access in code would assume this was 12th January 2019. You would get very different and incorrect results. To prove the point here is the same query simply switched to SQL view in the query designer:
Access has transformed the date into mm/dd/yyyy format.
To get around this, it is best to always format dates in code in an unambiguous way. To do this we would recommend using a function that takes a date from a form or input box and formats it into a useable format. Traditionally this would have been to format the date as “mm/dd/yyyy”, however it actually makes more sense to take the formatting one step further and use a format in a way that both you and access can clearly resolve.
In the VBA editor create a new Module if you don’t have one already – we’ll call it modBasicFunctions. Write the following code – it’s two functions one for Access and one if you ever pass criteria directly to SQL Server.
Now you can use this everywhere to always get the date in the correct format for your form driven code.
During debugging you can clearly see what the value is that you are passing in your code is without worrying that the international date formatting is tripping you up!
Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.