Date/Time format

[mp3909]

I am exporting the result set of query into a csv file using to DoCmd.TransferText method.

The table query shows the date columns as just DD/MM/YYYY which is what I want.

But when I open up the csv file after exporting the data, those columns have "00:00:00" appended in the cells so it is now like "DD/MM/YYYY HH:MM:SS".

How do I solve?

[JoeM]

Replace your original date field in your query with this calculated field:
CalcDate: FORMAT([Original Date Field],"dd/mm/yyyy")

That converts it to a text value, so it won't add anything to it, and be exported exactly as it looks.

[CJ_London]

The table query shows the date columns as just DD/MM/YYYY which is what I want.

to add to your understanding, what you see is the result of using a format property, the underlying value remains unchanged. When you export or import data, it is the underlying value that is used, the format property is not exported. What Joe's solution does is use the format function - which converts the underlying value into a text field which is then exported instead of the underlying value.

[mp3909]

Is it possible to do this for a union query?
I can't view a union query in design view, so I am wondering how it is possible to do as per your instruction.

[mp3909]

It's ok, I guess the only workaround this is to create a query with the underlying tables being the tables derived from the union query.

[CJ_London]

Is it possible to do this for a union query?

you have to edit the sql