What is the correct date format?

[surjamankhatiwora]

Hello Everyone!

I'm using VB6 with MS Access in back end. And the problem is when I add the records, the date format that I get from date picker is not entered into the table as intended. See example below for more clearity:

Dim strDate as String
strDate = "#" & DTPicker1.Day & "/" & DTPicker1.Month & "/" & DTPicker1.Year & "#". Also I even tried just DTPicker1.Value that too didn't work.

And in back end in table date field I'm using Short Date format like 16/6/2007.So When I entered 3rd Feb,2010(03/02/2010). It's just coming opposite that is 2nd Mar,2010(02/03/2010) in the table.

Therefore, Can any expert could help me & I would be grateful to you please...

[Stewart Ross]

Hi. Access date literals should really be entered in ANSI (American) date format (#m/d/y#), not UK format (#d/m/y#). Access will convert some UK date literals in #d/m/y# format correctly because there is only one way to interpret them - so #31/1/2010# will indeed represent 31 January. But, #3/2/2010# will be interpreted as 2 March as you have found.

You could simply change your date literal to #m/d/y# form as follows:

strDate = "#" & DTPicker1.Month & "/" & DTPicker1.Day & "/" & DTPicker1.Year & "#"

This does not change how the formatted date will be displayed at a later stage; it is just a means of entering the date correctly from your date picker.

You mention having tried the value returned by the date picker; if it is returning a true date value you should be able to enter that value directly into a variable of type date-time without using a date literal at all.

Anyhow, see how you get on with changing the date literal to #m/d/y# form.

-Stewart