VBA "Format" Function Returns Inconsistently -

Question

I need to format these dates into yyyy/mm/dd to query a MySQL database - looking at these two statements, you'd think they would produce the same result, right?

dateOne = Format(#9/1/2013#, "yyyy/mm/dd")
dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")

dateOne is now equal to 2013/09/01 , while dateTwo is equal to 9/1/2013. Am I crazy? This happens at the very begininng of my script, I've got Option Explicit on and I know it's saving to the right variables..

Sub main()

Dim dateOne, dateTwo As Date
Dim answer As Integer
answer = MsgBox("Would you like to generate a Transfer Report?", vbYesNoCancel + vbQuestion)
If answer <> 6 Then Exit Sub

'dateOne = InputBox("Input the first (earliest) date in mm/dd/yyyy format")
'dateTwo = InputBox("Input the second (latest) date in mm/dd/yyyy format")

dateOne = Format(#9/1/2013#, "yyyy/mm/dd")

dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")

Now dateOne is 2013/09/01, and dateTwo is 9/1/2013.

I'd originally had them at different dates, to query a range- before banging my head into my keyboard, to make absolutely sure I was performing the function the same way on both, I copied the text in dateOne, pasted it a line down, and changed the variable to dateTwo. Exact same command, executed two lines apart, producing different results. The script is almost done, but how, why, is it doing what it's doing?

Maybe I could ask the user to enter it in yyyy/mm/dd format, but I really want to know why this is happening. Can anyone help?

Answer 1

There are two things wrong with your routine that are combining to produce these seemingly illogical results:

Sub main()

Dim dateOne, dateTwo As Date
Dim answer As Integer
.
.
.
dateOne = Format(#9/1/2013#, "yyyy/mm/dd")    
dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")

First, Format(..) creates a Variant String, but you appear to be treating it as though it makes a Date.

Secondly this line: Dim dateOne, dateTwo As Date does not declare two Date variables as you likely believed when you wrote it. Rather, it declares dateTwo to be a Date variable but it declares dateOne to be a Variant (the default) because you didn't make it anything else. To make them both Dates you would need Dim dateOne As Date, dateTwo As Date.

So taking these two facts together, what happens to dateOne is that Format makes the string as you intend it to, and then since the target variable is a Variant, stores it there as a Variant String.

dateTwo works differently, however, as it first makes the date-string as you tell it to ("2013/09/01") but then sees that you are telling it to assign a String to a Date which are different data types, so it then converts the string back into a date, but uses your local system's NLS settings to determine how to interpret the string which (in your case) causes the month and day numbers to be swapped.

Clear?

Answer 2

You are experiencing a classic VBA gotcha. You're declaring DateOne as a Variant and DateTwo as a Date. To fix, change it to:

Dim dateOne As Date, dateTwo As Date

To read more, see this Chip Pearson page and scroll down to the section called "Pay Attention To Variables Declared With One Dim Statement."