Export to CSV - Date format going wrong (1 Viewer)

Scaniafan

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
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
Code:
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

AWF VIP
Local time
Today, 15:23
Joined
Jan 11, 2013
Messages
3,852
is the semi-colon a typo?
format([Departure Date];"dd-mm-yyyy")
 

Scaniafan

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
No, that is a Dutch language setting
 

namliam

The Mailman - AWF VIP
Local time
Today, 20:23
Joined
Aug 11, 2003
Messages
11,696
is the field formatted as a date in your export specification?
 

Minty

AWF VIP
Local time
Today, 19:23
Joined
Jul 26, 2013
Messages
10,355
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:23
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
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:rolleyes:

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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:23
Joined
May 7, 2009
Messages
19,169
if the date portion only use DateValue([Departure Date]), it will return true date value, not string, minus the time portion.
 

Scaniafan

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
I've now build below as test code:

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

Registered User.
Local time
Tomorrow, 06:23
Joined
Jul 4, 2013
Messages
2,770
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

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
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

AWF VIP
Local time
Today, 12:23
Joined
Oct 17, 2014
Messages
3,506
I created a query named qryDepartureDates with the following SQL

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

And exported it with

Code:
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

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
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

AWF VIP
Local time
Today, 19:23
Joined
Jul 26, 2013
Messages
10,355
Try single quotes within VBA - 'dd-mm-yyyy'
 

Scaniafan

Registered User.
Local time
Today, 12:23
Joined
Sep 30, 2008
Messages
82
Ok, works like a charm for the export part in date format. Strange since I already tried that, probably made some mistake somewhere...
 

Users who are viewing this thread

Top Bottom