I haven't worked with dates much lately, and to add to my forgetfulness on their characteristics is the fact that I use US date format, which means all is easy. So I will take a stab and hope my memory serves. First, the format function returns a string, which for display purposes ought to be OK. However, in your query you use just the date value that the query returns you might be better off. Not sure why you think you need to format a date for a query. An actual date value is stored as a type of double (number) but the format function can display the date in a lot of ways without altering the value. However, if you format 02/18/2018 (February 18) as 18/02/2018 Access will likely return nothing as there is no date with an 18th month (US format). In some cases such as 05/05/2018 getting results would be hit and miss because that one is a valid date regardless of which way you look at it. So remember that how you format your dates means nothing as far as how the values are actually stored. I think you are better off just passing what is expected and format your form and report controls instead. You might see 18/02/2018 in your table field, but the value it represents is really 02/18/2018.
Last edited by Micron; 04-07-2020 at 08:47 AM.
Reason: clarification
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.