Thread: Date format problem when exporting to csv file

I have created a macro to export the contents of a query as a csv file, but when I open the csv file, the date is showing the date and time. I need it to only show the date as this csv file is uploaded to a parcel carrier's system that will only accept this field as dd/mm/yyyy.

The date is created in an access table as a default value in the cell. The format is set to "Short date", the input mask to "00/00/000" and the default value to "Date()".
The query field is also set the same. The date format appears correctly in both the table and the query, but when it is exported it somehow changes to date / time format.

When I checked the cell format in the csv file it shows as a custom format "dd/mm/yyyy hh:mm"
This file is overwritten every time I run the macro and in fact when I ran it for the first time, it created the csv file so it can't be due to a preformatted file problem.

Would greatly appreciate any help on this - it's prevent us from automating our despatch processes.

You could modify your macro to either reformat the date prior to save or to create a temporary CSV fule which you then process to create the real one, again with reformatting.

To reformat, you could try using the LEFT function specifing the 10 left most characters which will trim off the time.

If you need extra help, you will need to post a copy of the CSV file so that folk can see the format.

In your query, create a new field with the expression =Int([your field name containing date]. Export this and not the current date field. Access saves dates as a serial number and formatting is only a presentation. It is not changing the value. This is especially important if you are having Access create the date as it will create it as a value similar to 43567.12387 which is the date and time in serial format. This is how it is saved. Formatting changes only how you see it.

Alan