Format function vba changing date

Question

The format function in vba is changing the date. e.g for format("3/12/2009","DD/MM/YYYY"), the function returns 12/03/2009 where "3/12/2009" is what excel vba reads from a cell that has the value 12-Mar-2009 and the format as "dd-mmm-yyyy"

Answer 1

No it's not.

If a date-as-string is passed to the Format function, it will parse it using current regional settings. Your settings are obviously MM/DD/YYYY which is default for USA. Nothing prevents Excel from displaying a date as DD/MM/YYYY if set manually, but by default it would display MM/DD/YYYY.

To do: Stop reading dates as strings. Read them as dates.

dim d as date
d = activecell.value

Answer 2

Had few times problem myself where VBA in Access reades most dates as europian but some as USA:

This DOES NOT work properly:

myRs.FindFirst ("Date =#" & myDate & "#")

This works:

myRs.FindFirst ("Date =#" & Format(myDate, "Long Date") & "#")

The long date (eg 01 January 2012) clearly makes the difference between month and day