Export to CSV - Date format going wrong

[Scaniafan]

Hello all,

I've got a small challenge that I cannot seem to get solved. I'm exporting a query (QRY_Gross_Performance_4) to a CSV file via a SELECT SQL in VBA, since I need to apply some variable filters on it.

I've got a field QRY_Gross_Performance_4.[Departure Date] that is formatted in to

format([Departure Date];"dd-mm-yyyy")

in QRY_Gross_Performance_4.

Running the QRY_Gross_Performance_4 perfectly shows the date as 01-06-2016. However, when I export to the CSV, I get 1-6-2016 00:00 in the field.

Am I doing something wrong?

 

[moke123]

is the semi-colon a typo?
format([Departure Date];"dd-mm-yyyy")

 

[Scaniafan]

No, that is a Dutch language setting

 

[namliam]

is the field formatted as a date in your export specification?

 

[Minty]

All in dates Excel / VBA all dates have an assumed time portion that is actually stored as the decimal part of the number used to represent all dates.
So today 01/06/2016 00:00:00 is actually stored as 42522.0000
Today at 1:00pm is 01/06/2016 13:00 is actually stored as 42522.541666667

If you format the date in excel as a short date it will display correctly.

 

[arnelgp]

don't use any Format() since doing this will result in a string (text) and not true date format. just leave it as is:

[Departure Date]

 

[Scaniafan]

is the field formatted as a date in your export specification?

I'm not that well known around export specifications, but in the box "Field Information" I've got one record (as test)

Field Name
Departure Date


The Dates, Times and Numbers is set to

Date Order DMY
Date Delimiter: -
Time Delimiter: :
Four Digit Years: checked
Leading Zeros in Dates: checked
Decimal Symbol: ,

All in dates Excel / VBA all dates have an assumed time portion that is actually stored as the decimal part of the number used to represent all dates.
So today 01/06/2016 00:00:00 is actually stored as 42522.0000
Today at 1:00pm is 01/06/2016 13:00 is actually stored as 42522.541666667

If you format the date in excel as a short date it will display correctly.

That part I do understand, however is not an option. I'm working to create CSV files I'm going to send out to over 200 individual forwarders and I'm aiming to generate these files by one click on a button.

I'm not eager to open 200 separate files and change the date format every week again and again

don't use any Format() since doing this will result in a string (text) and not true date format. just leave it as is:

[Departure Date]

So you are basically saying that there is nothing that can be done to export only the date? Doesn't even matter to me whether this is d/m/yy or dd/mm/yyyy or what ever, as long as it doesn't include the time stamp 00:00

 

[arnelgp]

if the date portion only use DateValue([Departure Date]), it will return true date value, not string, minus the time portion.

 

[Scaniafan]

I've now build below as test code:

Dim testSQL As String
            
            testSQL = "SELECT DateValue(QRY_Gross_Performance_4.[Departure Date]) as Departure INTO TBL_Test FROM QRY_Gross_Performance_4"
                                           
            DoCmd.RunSQL testSQL
            
            DoCmd.TransferText acExportDelim, "TBL_Test", "TBL_Test", CurrentProject.Path & "\Test.csv", True

This runs the export but still with 00:00 in the date field.

 

[Cronk]

Going back to the first response, semi colon might be dutch but format () is US. Use a comma.

Secondly, CSV is a text file. Are you viewing the results with a text editor, or Excel which is set at dutch formatting?

 

[Scaniafan]

Going back to the first response, semi colon might be dutch but format () is US. Use a comma.

Secondly, CSV is a text file. Are you viewing the results with a text editor, or Excel which is set at dutch formatting?

Ok, didn't know the first part, thanks.

Second, I'm always viewing in both, but do expect that the forwarders that I'm going to share the file with, view it in Excel with local regional settings. Which may vary from US to Chinese

 

[sneuberg]

I created a query named qryDepartureDates with the following SQL

SELECT Format([Departure Date],"dd-mm-yyyy") AS FD
FROM tblDepartureDates;

And exported it with

DoCmd.TransferText acExportDelim, , "qryDepartureDates", "C:\Users\sneuberg\Desktop\export.csv"

And the results are correctly formatted when viewed in a text editor, e.g,

"13-06-2016"
"19-06-2016"
"18-06-2016"
"01-06-2016"
"02-05-2016"

So this seems to work if you format the date in the query.

 

[Scaniafan]

I've been looking in to your suggestion, which works indeed when I'm creating a query out of it. However, I've got a step in my database where I filter on forwarders selected in a multiselect listbox, which can only be done via VBA.

If I try your suggestion in VBA I get a Compile error: Syntax error, due to the quotation marks around "dd-mm-yyyy", which I can't get solved.

 

[Minty]

Try single quotes within VBA - 'dd-mm-yyyy'

 

[Scaniafan]

Ok, works like a charm for the export part in date format. Strange since I already tried that, probably made some mistake somewhere...