Exporting date to Excel causes day/month to reverse 2

[MeldrethMan]

I'm exporting data from a form to Excel but dates are coming out as mm/dd/yyyy even though I've include a format instruction like this

Set rst = CurrentDb.OpenRecordset("Select * From tblSelectedClients")
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(2,1).Value = Format(rst!DateEntered("dd","mm","yyyy")
etc


The source table tblSelectedClients is showing the dates like DateEntered correctly as dd/mm/yyyy.

 

[Remou]

What happens when you use transferspreadsheet?

http://lessthandot.com

 

[dhookom]

Your code is missing a parens

objSht.Cells(2,1).Value = Format(rst!DateEntered("dd","mm","yyyy")

Please provide the actual code from a copy and paste the actual code.

Have you looked at the results in a debug window or after inserting a breakpoint?


Duane
Hook'D on Access
MS Access MVP

 

[tedsmith]

Have you got all computers set to the same correct region date format?(including the one that the excel sheet was designed on)

I had trouble some years ago using Windows2000 where a date query would only work if I converted it to USA format first even though the computers were all set to Australia
It now does not appear necessary if the OS is XP later

 

[MeldrethMan]

Duane

My mistake sorry, copied manually from other computer and got it wrong. This was the correct line of code

objSht.Cells(2, 1).Value = Format(rst!DateEntered, "mm/dd/yyyy")

I've tried setting a break point as you suggest and DateEntered shows correctly on hover but Debug.Print Format(rst!DateEntered, "mm/dd/yyyy") reverses day and month.

I included the Format because the date was coming out as 47107, for example.

 

[dhookom]

Set the date format in the Excel format properties.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Since you're apparently working in one of those places that use the British date format, Allen Browne's paper on the the use of non-US dates may be of help:

Your text to link here...

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[tedsmith]

< one of those places that use the British date format
Rather I would say "one of those places that use the normal date format dd/mm/yyyy"
I would think USA format is the odd one out.

 

[missinglinq]

Not when you're speaking of Access! The Magical Forest of the Access Gnomes is in Redmond, Washington, U.S.A.

(I know, the white S is hard to see!)

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[MeldrethMan]

Thanks all.

Duane - dates set to dd/mm/yyyy in Control panel, is there somewhere else?

Missinglinq - the Allen Browne paper explains that Access can misinterpret dates, thanks. Using his suggestion of adding # to either end of the VBA code line does give the right format but with a '#' at either end in the exported Excel worksheet.

What works simply is just reversing dd/mm like this, Format(rst!DateEntered, "mm/dd/yyyy") but wonder if there are possible hazards in this.

 

[SkipVought]

When you enter a date in Excel, Excel 1) looks at the CHARACTERS that you have entered and 2) makes an educated guess that you intended to enter a date and 3) CONVERTS the CHARACTERS that you entered, assuming that your date entry structure was MONTH, DAY, YEAR, parsing the CHARACTERS accordingly and returning a NUMBER, that can then be FORMATTED to DISPLAY in whatever form you like.

If you enter last Friday's date as
[tt]
11/5/2012
[/tt]
Excel assumes that it is NOVEMBER 5, 2012!

If you enter today's date as
[tt]
14/5/2012
[/tt]
Excel assumes that it is NOT a date and just leaves it as TEXT. It cannot be used in date calculations and it will not collate correctly.

My advise is to enter dates in Excel in the UNAMBIGUOUS YEAR, MONTH, DAY structure and then FORMAT to DISPLAY as you expect.

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]