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.
16  12499 
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 14,534
Recognized Expert Moderator MVP
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
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
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.
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 14,534
Recognized Expert Moderator MVP
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.
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 14,534
Recognized Expert Moderator MVP
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Fran Zablocki |
last post by:
I have a process that exports an Access table to a comma-delimited
text file. One of the fields that is exported shows the date it was
exported, using the Date() function. In the Access table, the date
appears in the records in mm/dd/yyyy format, but after exporting, the
date appears as mm/dd/yyyy 0:00:00. For example, today is 11/06/2003.
It shows 11/06/03 in the Access table, but the exported text file
shows 11/6/03 0:00:00. I thought...
|
by: D |
last post by:
I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc. Only
one problem: one of the fields is a "SchoolYear" TEXT field that contains
data such as 2000/01, 2001/02, etc. If I export a Query with this kind of
data to Excel, it gives me the text value of this field; however, when I
export a Report bound to this TEXT field, Excel gives me the values 36526,...
|
by: Otis Hunter |
last post by:
I have been fighting with this for days and your expert help is
needed! Below is the code I am executing which results with "Object
doesn't support this property or method". The error is occuring on
the "With Me.OLEObject" line. I am trying to extract a Word
document(OLE object) from an Access database, for each record in the
table, and save it as a Word Document. The 4th field of each record
is a OLE object type.
I may be getting...
|
by: Big Time |
last post by:
I'm exporting a large amount of data from one of my tables into a tab
delimited text file. The problem is the fields that have date format come
out in MM/DD/YY HH:MM:SS format when what I want is to have the dates come
out simply as MMM/YY i.e. Jan / 04 instead of 1/1/04 00:00:00 . I have
tried using export specs to get this to work but there is no option to drop
the DD and have it export simply as Jan/04. Is there any way around this?
|
by: warlord |
last post by:
In order to save typing, I've borrowed the text from a post of nearly 12
months ago.....but the problem still exists.
I've been banging my head all day with this, so I'm hoping someone has
some suggestions for me:
I'm trying to either print or export some Crystal Reports in asp.net
web application. The reports were made with CR 9.0, get their data
from an SQL Server 2000 database.
| | |
by: Kevin Chambers |
last post by:
Hi all--
In an attempt to commit an Access MDB to a versioning system (subversion),
I'm trying to figure out how to convert a jet table's metadata to text, a
la SaveAsText. The end goal is to be able to build an MDB completely from
the svn repository text files.
Has anybody dealt with this?
Thanks in advance,
|
by: czarbjones |
last post by:
Hey Gurus,
I'm trying to export a query (QueryDataExport) to a comma delim CSV file. The file name should look like this "C:\DSF_20061127_124211.csv" however it is exporting as "C:\DSF_.csv".
Also all of the text fields and header are being exported with quotes "" around them and I'm unsure how to turn this off. Do I have to actually have to create a rst and then code it to go through and remove these manually or is there a function...
|
by: sara |
last post by:
I have what I think is a little strange...I have to get data from our
payroll system into a specific format (fixed record length) as a .txt
or .prn file only to upload to our 401k custodian.
I can get the data into Access (from Payroll) and write a query with
all the fields (inserting fields with spaces, as needed), but I can't
figure out how to get it to export to a .txt file - without the field
name header row.
I don't really ...
|
by: jrnowlan |
last post by:
I have the following code that takes form data from a picklist and uses the input values to query an access database. Currently everything works, but it writes the results to the same page when submit is selected. I would like (if possible) to write the results to a csv file that could be saved on the users computer. Is this possible? If so can some one give me a little help with this ?
Here is the code that I have.
<html>
<title>ECVS...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
