Exporting Date Field to Text (Access 2000 – SR1)

Topic

New Reply

I’m exporting a query that contains dates. When the export wizard comes up, the date includes the time format, even tho I have it formatted in the query as mmddyyyy.

I found an article on KB (Q209268) and tried the first method suggested, which was to create a calculated field with the date and put the format in that. Looks great in the query, but I still get the same thing on the export.

The 2nd method talks about using a file format of “Text (Fixed Width)” which I can’t find anywhere. Does anyone know how I can get this option? I want a fixed width file, so this could be a solution. Just can’t find any reference to it.

Any other suggestions or solutions? Any idea why the first solution offered by Microsoft doesn’t work? Could there be a setting somewhere in Access that I’m missing?

Any help appreciated!
thanks.

Reply | Quote

Replies

I assume that you are using the export feature from the main menu by selecting File / Export.

– After you select File / Export, select the file name as required and set the Save as Type as Text.
– Select Save (The Export Text Wizard will start)
– Select the Advanced Button
– Under File Format, Select the Fixed Width Radio Button
– Select the field for the Date in the Field Information Box
– Reduce the Width to the desired characters (ie., for a date format of 12/12/2001, set the value to 10, the length of the string
– Select OK and review the data to be exported. Re-set the Width as necessary.

Good Luck

Reply | Quote

Another possible solution: If you are just trying to get the query results to a text file, try exporting as type “Microsoft Word Merge(*.txt)” instead of as type “Text Files”. This does not bring up the export wizard. The results will be a text file with your query fields delimited by tab spacing. In your case, this may be all that you need, but you’ll learn more about exporting, if you use Gary’s suggestion.

Reply | Quote

Thanks for both of the suggestions.

I may have been going into “Advanced” one screen too late in the wizard.

Another thing that made a difference was the first rows returned in my query didn’t have anything in the date fields. When I changed the order of the query so that the first rows that show in the wizard have data in them, it seemed to work. It kept the formatting that I created with the calc statement in the query. For some reason those nulls showing up first was throwing it off.

Thanks for your help.

Reply | Quote