This MSAccess tutorial explains how to use the Access DateValue function with syntax and examples. The Microsoft Access DateValue function converts a string to a date.
One thing to bear in mind as you are using dd/mm/yyyy date format...
Using DateValue in VBA will give an output in mm/dd/yyyy format except for unambiguous dates such as 31/07/2020.
For example
Code:
?DateValue(#04/11/2019 11:35:45#)
11/04/2019
One way of handling that is to format it first as follows
Unfortunately it is necessary when using VBA code for those in the UK and much of the world who use dd/mm/yyyy date format.
The examples I gave in post #3 were copied direct from my immediate window
Here are two more examples
Code:
?DateValue(#11/07/2019 11:35:45#) 'ambiguous date so treated as 7 Nov 2019
07/11/2019
?DateValue(#21/07/2019 11:35:45#) 'unambiguous date =21 July 2019
21/07/2019
If you have dates stored in a table and run a query selecting DateValue([DateTimeField]) it will return the date correctly for 11th July 2019 11:35:45.
The date is stored correctly even though it displays in UK format 11/07/2019 11:35:45, and thus the function evaluates '11/07/2019' in the query result.
The VBA code you posted is also working correctly. The ambiguous date you pass to the function is 7th November. In VBA you always write dates in US or ISO unambiguous format even if you code in the UK! (especially since you are passing a date delimited as a date with octothorpes, and not a string representation of a date).
You do have to be careful if you pass a string date to the function as the date will first be evaluated with local rules:
No need. We are I believe saying much the same thing
Of course, queries will always resolve the dates correctly
Perhaps I should have made it clear I was referring to using this in VBA - I thought this was implicit in providing output from the immediate window.
Anyway, I have now made that explicit in the earlier posts.
I agree completely that the code is behaving correctly - my intention was purely to stress the outcome(s) to the OP
I'll try to make my point again using the 'ambiguous' date of 11/07/2019 11:35:45 (11 July 2019)
Firstly lets consider date values being passed (as would be the situation for the OP)
Code:
'Testing date values
?(#11/07/2019 11:35:45#)
07/11/2019 11:35:45 'output in mm/dd/yyyy format
?Format(#11/07/2019 11:35:45#,"mm/dd/yyyy")
11/07/2019 'output in dd/mm/yyyy format
?DateValue(#11/07/2019 11:35:45#)
07/11/2019 'output in mm/dd/yyyy format
?DateValue(Format(#11/07/2019 11:35:45#,"mm/dd/yyyy"))
11/07/2019 'output in dd/mm/yyyy format
All of the above are behaving EXACTLY as they should
Now lets test string values ...as would be the case after formatting
Code:
'Testing string values
?("11/07/2019 11:35:45")
11/07/2019 11:35:45 'output in dd/mm/yyyy format
?DateValue("11/07/2019 11:35:45")
11/07/2019 'output in dd/mm/yyyy format
?CStr(#11/07/2019 11:35:45#)
07/11/2019 11:35:45 'output in mm/dd/yyyy format
?DateValue(CStr(#11/07/2019 11:35:45#))
07/11/2019 'output in mm/dd/yyyy format
Once again, all of the above are behaving EXACTLY as they should
But still ?DateValue(#04/11/2019 11:35:45#) is 11th April, not 4th Nov. It's not clear (to me) that you're trying to warn that the date being passed is not what the OP might think it is. It came across more focused on the value returned 'appearing' flipped.
?DateValue(#11/07/2019 11:35:45#) 'ambiguous date so treated as 7 Nov 2019
07/11/2019
?DateValue(#21/07/2019 11:35:45#) 'unambiguous date =21 July 2019
21/07/2019
Nothing ambiguous about a date with # delimiters. It will be interpreted as an mm/dd/yyyy date regardless of the regional settings.
A date as a string is interpreted in the regional format. Nothing ambiguous there either.
Dates are expected to fall between 1 January 100 and 31 December 9999.
The problem is when dates are invalid in these contexts, there is a stupid fallback in Windows (not just VBA or Access) where it attempts to make them work in any format. It will even try yy/mm/dd which is why 29/2/21 will be interpreted as 21st February 1929.
I would have preferred they simply throw an error.
you try it with tables with those values.
and not Constant feed to DateValue().
because what the OP showed is in datasheet.
the Output will be whatever Locale date you set.