I'm unable to format the date fields when I export from Access 2000. I'm trying to generate a report that has to be comma delimited, strings in quotations, and date as mm/dd/yyyy. Everything looks the way it should until I export. I get mm/dd/yyyy 0:00:00 everytime.
There are several tables used in a query. The query results are saved in another table. I've tried formatting the date fields in the original tables, in the query design, and in the result table. I've tried the 'short date' format and mm/dd/yyyy in the tables, and Field1: format([date1], "mm/dd/yyyy"), Field1: left([date1], 10), and field1: format([date1], short date) in the query. All combinations return the time when I export to a text file.
The only time the zeros go away is when I export the date field as text, but then I get the quotation marks. Any ideas? Is there something I can do in Excel or a macro I can create that will format the fields as needed and generate a text file? Anything? Thanks in advance.