Exporting date fields to a text file

[pmatzek]

I am attempting to automate a process involving importing a .txt file into an existing table, changing a date in each record, then exporting the table, again as a .txt file. One field contains a date (yyyy-mm-dd) and another a date/time (yyyy-mm-dd hh:nn:ss). If I do the export manually, the exported data in the .txt file is correct, but if I automate the process using the transfertext action and an export specification, the date field exports with time (00:00:00) added to the date. It does this whether those fields in the existing Access table are text fields or date/time fields. How can I prevent Access from adding time to the exported data in the date only field? The time characters don't appear in the Access table, only in the exported .txt file.

[pmatzek]

I am attempting to automate a process involving importing a .txt file into an existing table, changing a date in each record, then exporting the table, again as a .txt file. One field contains a date (yyyy-mm-dd) and another a date/time (yyyy-mm-dd hh:nn:ss). If I do the export manually, the exported data in the .txt file is correct, but if I automate the process using the transfertext action and an export specification, the date field exports with time (00:00:00) added to the date. It does this whether those fields in the existing Access table are text fields or date/time fields. How can I prevent Access from adding time to the exported data in the date only field? The time characters don't appear in the Access table, only in the exported .txt file.

I'm using Access 2003 on Windows 2000.

[MMcCarthy]

I am attempting to automate a process involving importing a .txt file into an existing table, changing a date in each record, then exporting the table, again as a .txt file. One field contains a date (yyyy-mm-dd) and another a date/time (yyyy-mm-dd hh:nn:ss). If I do the export manually, the exported data in the .txt file is correct, but if I automate the process using the transfertext action and an export specification, the date field exports with time (00:00:00) added to the date. It does this whether those fields in the existing Access table are text fields or date/time fields. How can I prevent Access from adding time to the exported data in the date only field? The time characters don't appear in the Access table, only in the exported .txt file.

Regardless of how the field is formatted Access stores it with the time. Try changing the format of the field to "mm/dd/yyyy" and see if that works.

Mary

[Shal]

I have the same issue.
I run a script that is simply dumping a table into a text file for another user to pick up. The attributes of the date's fields are set to "Short Date" and they display only the date in Access. The issue comes when the text file is viewed, all the rest of the data is fine but the Date has had a time added. The time fields have also had a date from year 1899 added. This is an issue for me as I need to export into fixed length fields.
I changed the attributes of the date and time fields in Access to be dd/mm/yyyy and time of 00:00 in the Input Mask but to no avail.

Please help

By the way I am also using Access 2003

[Shal]

I was able to fix this in the most part by defining the dates as text in the Export Specification while leaving them as Short Date etc in their attributes in the table in Access.

My only issue now is that the last 2 spaces of my date field are being cut off in the text file such as below:

339581 20/03/20 9:51:00 20/03/20 9:52:00
434106 27/03/20 3:11:00 27/03/20 3:11:00
434107 30/03/20 4:31:44 30/03/20 4:31:53

The width of each of the date fields is 10 so I don't know why only the first 8 are showing. The width of the time fields is 8.

[Shal]

I now have set the date field to be only 10 characters in the Export Specification and this chops off the added on time for the date field. So the date information in the text file is now usable.

I can't do this for the time fields though because the Date gets added on before the time is printed and you can't tell the Export specification to only start from somewhere after the beginning of that field. I also ask for the time in Short Time format which is supposed to (and does in the datasheet view in Access) display the time in 24 hour time. This then stops me changing the format of the time field to text in the export specification as I don't get the 24 hour time display and if I use medium setting for the time in Access I don't get the AM/PM part exported unless I choose Date/Time as the export format for it.

Interesting....

[MMcCarthy]

I now have set the date field to be only 10 characters in the Export Specification and this chops off the added on time for the date field. So the date information in the text file is now usable.

I can't do this for the time fields though because the Date gets added on before the time is printed and you can't tell the Export specification to only start from somewhere after the beginning of that field. I also ask for the time in Short Time format which is supposed to (and does in the datasheet view in Access) display the time in 24 hour time. This then stops me changing the format of the time field to text in the export specification as I don't get the 24 hour time display and if I use medium setting for the time in Access I don't get the AM/PM part exported unless I choose Date/Time as the export format for it.

Interesting....

Open the export wizard and don't open the advanced tab. In the window which shows the lines which separate the text fields for export try dragging the lines to the appropriate places. Then open the advanced tab and see what lengths wizard has given to the fields.

If your data is stored as text try using quotes on the text fields and see if that helps.

[Shal]

Thanks for the reply.

I tried to move the lines separating the text fields. They moved fine, the reason it doesn't help me is that you can move the right side of a field and adjust the field's width but not the left. If you move the left line of the field it narrows the field to the left of the line not to the right. Hence I can't chop off the start of the text in a particular field and can't remove the added in Date reference to a time field.

The time is an auto generated time in the Access database using the method =Time(), unfortunately this means the data coming to me in the table is not readily changed by means of adding quotes. I would still want it as time anyway and the only place I have changed it to text is in the export specification.

I actually need the time to be printed out in the text file in the format 00:00 as a minimum but prefereably 00:00:00. My other issue is that I need leading zeros on this field as well if the time is less than 10am in the morning. EG - 09:51:52

Maybe Access just isnt capable of doing this and I need to write a procedure to pull that field out of the recordset and format it and then write it back in again.

What do you think?

[MMcCarthy]

Thanks for the reply.

I tried to move the lines separating the text fields. They moved fine, the reason it doesn't help me is that you can move the right side of a field and adjust the field's width but not the left. If you move the left line of the field it narrows the field to the left of the line not to the right. Hence I can't chop off the start of the text in a particular field and can't remove the added in Date reference to a time field.

The time is an auto generated time in the Access database using the method =Time(), unfortunately this means the data coming to me in the table is not readily changed by means of adding quotes. I would still want it as time anyway and the only place I have changed it to text is in the export specification.

I actually need the time to be printed out in the text file in the format 00:00 as a minimum but prefereably 00:00:00. My other issue is that I need leading zeros on this field as well if the time is less than 10am in the morning. EG - 09:51:52

Maybe Access just isnt capable of doing this and I need to write a procedure to pull that field out of the recordset and format it and then write it back in again.

What do you think?

OK, now I understand what you are trying to do.

Create a separate field to enclose the section you don't want exported. Now in the Advanced wizard under Field information there is a tickbox column called skip. Tick the box beside that field and it won't export.

[Shal]

Thanks again, seemed like an easy solution but I must be doing something wrong. When I went into the export specification and added a new field so that I could chop the time into date and a time sections, trying to save that spec or hit ok brought up the error message :

"The number of fields in your export specification does not match the number of fields in the table you have chosen to export."

Even so, when I move the lines (that define the widths of each field) to get just the date portion of the time field, the time part disappears as if covered over by the field to the right. So I wouldn't be able to set any other field to be the time part of this field.

[Shal]

I created a dummy extra field in the Database in case this is what was suggested and re set up the Export Specification. I then checked if I could set the start and end of fields to split up what is getting exported for the time reading but could not.

[MMcCarthy]

I created a dummy extra field in the Database in case this is what was suggested and re set up the Export Specification. I then checked if I could set the start and end of fields to split up what is getting exported for the time reading but could not.

In your query do the following ...

Format([DateField], "dd/mm/yyyy") As DateSection, Format([DateField], "hh:nn" As TimeSection)

instead of the DateField as it is now.

[Shal]

Now I am getting somewhere, thanks for that.

I had to re-write my query in the module because it was just a
SELECT * FROM XXXX
type one before, but with the formatting in it I am pretty close to done now. The other thing I think I was missing is that the Export Specification needed to be applied to the query not the table.

I am getting an error telling me XX records will be deleted each time I run it but I can't see anything in the table being deleted. Is this just referring to the query records?

I appreciate all the help

[MMcCarthy]

Now I am getting somewhere, thanks for that.

I had to re-write my query in the module because it was just a
SELECT * FROM XXXX
type one before, but with the formatting in it I am pretty close to done now. The other thing I think I was missing is that the Export Specification needed to be applied to the query not the table.

I am getting an error telling me XX records will be deleted each time I run it but I can't see anything in the table being deleted. Is this just referring to the query records?

I appreciate all the help

This sounds very strange. That warning usually only comes up when you are deleting records from the table.

[Shal]

Other than the error message coming up everything is now fine. I also can't find anything actually being deleted so I am running with it for now.

Thanks again for the help

[jamjar]

This sounds very strange. That warning usually only comes up when you are deleting records from the table.

I'm having the same problem. It occurs if I use Format in my query to try to control the date/time text output. The DELETE warning appears when I perform the TransferText and if I proceed, no source records are deleted, but what I'm seeing is that the formatted date fields are not exported to my text file (blanks appear where the dates should be).

If I remove the Format function from my query there is no Delete message, and all fields are exported, just in the wrong format ....

[jamjar]

I'm having the same problem. It occurs if I use Format in my query to try to control the date/time text output. The DELETE warning appears when I perform the TransferText and if I proceed, no source records are deleted, but what I'm seeing is that the formatted date fields are not exported to my text file (blanks appear where the dates should be).

If I remove the Format function from my query there is no Delete message, and all fields are exported, just in the wrong format ....

The Delete warning appears in my case because I forgot to change the Export Specification to recognise the Date field as Text after using the Format function. Once I fixed this, the TransferText works fine.

James