Change field from date to text using text

[quijote]

I need to output a query result as a text file. The format need it is the following "000005","12/18/70" (comma delimited)the first field is account # and the next is the Date account open, as you can see both need to be in text format. When I try to import the query result to a text file, when I open the file the date came up like this "000005",12/18/70 00:00:00. The only way I can do to show me the output in that format is by changing the data type from date to text in the table containing the data. I have to do this weekly so I was planning to copy the original table past it with a different name and then change the format of the field. I want to know if there is way to change the format using code so I do not have to do it manually every time. Thanks

 

[dcx693]

Use an expression like this in the field line of your query:
Format([YourDateField],"mm/dd/yy")

it uses the Access format function to output a date in a specified format.

 

[quijote]

Hi,
I tried that but in the ouput file still shows up as date field

 

[dcx693]

Is the query outputting to the text file? The column that you create using that function should be formatted as mm/dd/yy. Is it?

 

[Waltang]

Can you post the the SQL statement of your query? I think dcx has the right solution, but that you are misunderstanding where he wants you to put it.

 

[quijote]

Here is the sql
SELECT JAM.[Account #], Format([first day activity],"mm/dd/yy") AS [Date]
FROM JAM;

Thanks

 

[quijote]

Thanks for the help and I will stop using Date as a field name. It works great the problem was that I was using Output to instead of TransferText. Take care