CopyFromRecordset Date format issues

[papasmurfuo9]

Hi

i have a CopyFromRecordset into excel 2003

one column is a set of dates,
but when the code runs and copies the date column over, the format is as it should be
but alot of dates are showing in american format

Cant seem to figure out why, but its not every date, id say 50% of the 1500 records

any suggestions?

thanks

 

[pbaldy]

One solution may be to use the Format() function in the recordset:

Format(DateField, "dd/mm/yyyy")

 

[papasmurfuo9]

thanks for the reply, where exactly would i put this ?

in the query?, where would i set the formatting?

Field : Fld_Date_Account_Opened
Table : Tbl_Master
Total : Group By
Sort : Descending

from the query

With oXL
    .Sheets("Master Closed").Select
End With

Set oXLSheet = oXL.Worksheets("Master Closed")
Set Rst = CurrentDb.OpenRecordset("Qry_Master_Report_Closed")

    oXLSheet.Range("B7").CopyFromRecordset Rst
    Rst.Close

Set Rst = Nothing
Set oXLSheet = Nothing

 

[pbaldy]

Try

Field : DateOpened: Format(Fld_Date_Account_Opened, "dd/mm/yyyy")

 

[papasmurfuo9]

thanks,
still pulling through some with the month/date wrong way round

fine in the table, just not when copied to excel

 

[cheekybuddha]

Can you format the column or range after the import?

' ...
    oXLSheet.Columns("B").numberFormat = "dd/mm/yyyy"

(obviously change "B" to whichever column you require, or subsitute a range if you know its size)

hth,

d

------------
David Marten

 

[cheekybuddha]

Actually, you may want to use the .NumberFormatLocal property.

hth,

d

------------
David Marten

 

[vbaInet]

thanks,
still pulling through some with the month/date wrong way round

fine in the table, just not when copied to excel

Still in the query:

DateOpened: Format(Fld_Date_Account_Opened, "dd[COLOR="red"]\[/COLOR]/mm[COLOR="Red"]\[/COLOR]/yyyy")

Additions to pbaldy's suggestion in red.

If that doesn't work, then the suspect is Regional Settings. You would have to do it in Excel.