Automate Change of the format of a field to short date for export

[Chuck W]

Hi,
I have a table that I am creating through a make table query. Once created,
I want to export the table to a text file and save to a location. I am
attempting to create a macro that will do this. One of my fields is called
CMMonth. The problem is that when I export this table to a text file, it is
exporting this field as a long date with hours:minutes:seconds. The system I
am importing the text file to rejects the long date. I can only be M/D/Y. I
have tried changing the field format manually once the table is created but
it still exports it as a long date. How can I change this so that the format
is only a short date and also put the format change in a macro or in a query
that goes into a maco?
Thanks,

 

[John W. Vinson]

Hi,
I have a table that I am creating through a make table query. Once created,
I want to export the table to a text file and save to a location.

Why the extra step? There's a LOT of overhead in a MakeTable query, and it's
prefectly possible to just change the MakeTable query to a SELECT query, and
export *that query*.

I am
attempting to create a macro that will do this. One of my fields is called
CMMonth. The problem is that when I export this table to a text file, it is
exporting this field as a long date with hours:minutes:seconds. The system I
am importing the text file to rejects the long date. I can only be M/D/Y. I
have tried changing the field format manually once the table is created but
it still exports it as a long date. How can I change this so that the format
is only a short date and also put the format change in a macro or in a query
that goes into a maco?
Thanks,

The export process completely ignores the format settings of fields being
exported. The solution is to explicitly cast the date field as a Text, using
the Format() function. Rather than exporting [CMMonth] use an expression in
the query:

ExpCMMonth: Format([CMMonth], "mm/dd/yyyy")

or whatever format you wish.

 

[Daryl S]

Chuck -

If this table is being re-created each time, and only used for the export,
you can make the date field a text field, and the make-table query can format
the date to the string you need for the export.