Date field with 0:00 appended after using TransferText macro action

[lac55]

I have a query that formats a date/time field to eliminate the time portion. My query results show only the date, which is exactly what I want. However, when I use the TransferText macro action, the result for this field in the .csv file once again contains the 0:00. I have also tried a make table query and exported the table, but the result is the same. What can I do to keep the 0:00 out of the .csv file?

I read a previous post that indicates one person used a specification file to solve this same problem. If this is possible, how do I create a specification file?

[Mihail]

Only an idea:
Force your query field to a String data type.

for that use

YourFieldInQueryName:CStr(Formula that select only the Date part)

[lac55]

Thanks for your prompt reply.

I have tried all the following:
MyDate:CDate(Int([app_rec_date]))
MyDate:Format([app_rec_date],"mm/dd/yyyy")
MyDate:Cstr(Format([app_rec_date],"mm/dd/yyyy"))
MyDate:(Int([app_rec_date]))
MyDate:Cstr(CDate(Int([app_rec_date])))

I have tried formatting the field to a Short Date. I have tried a make table query and exported the table.

They all return the date without the time portion in Access. But, the 0:00 is still in the .csv file after the export.

Is there a way to specify that the date should not contain the 0:00 in a specification file? I am not currently using a spefication file in the macro that creates the query/table for export.

[Mihail]

This work fine for me

MyDate: Left([app_rec_date],10)

The Left() function have as result a string that contain only the Date (not the Time).

I notice (Access 2007) that, if I try to export the query itself (as .txt), the Delimiter option do not work. The delimiter is a space whatever I have choose.
So, I transform my Select query into a Make Table one then I export the created table.

[lac55]

I was able to use a make table query successfully since the date fields become, in essence, text fields in the table. The only downside is that now I can't sort my .csv field on any of the date fields because they are no longer date fields. Thanks for your help!!!

[Mihail]

Do the sorting in the query.

[NeoPa]

It's hard to answer properly as you give us no indication of what you're exporting, and which lines of the data have problems. Essentially, you need to undrstand the difference between a date value, and a date string. They look similar to the naked eye, but not remotely similar to Access. Understand this distinction and I'm sure you will be able to find an easy solution to your quandary.

[lac55]

I found a solution from another forum (is it ok to say that here?) that solved my problem. Here is the link (is it ok to do this?) to solve this for Access 2007.

http://social.technet.microsoft.com/...-e9f61d10d973/

Be sure to read all the way to the end.

[NeoPa]

As the link is to a Microsoft site (and they are the producers of the product in question) then yes, this is OK. Otherwise we would expect an answer to be posted as an explanation rather than a link.

My understanding of the solution is that the problem lies in the Export Specification. This may be able to be handled manually (In the Advanced section), but if not, then create the specification using textual data, save the specification, then use it later to export your numeric or date data.

In 2003, when in Advanced, there are a number of hidden columns for the column specs. If you drag across right from the Field Name column then double-click on the right side of the column, then all the invisible columns are resized and shown. If you specify "Text" for the Data Type column you should see it work more to your liking ;-)