Dutch-
This has always been a problem. The Excel import wizard lets you override
column data types, but the text wizard doesn't. I've forwarded this to a
private discussion group with the Access developers.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Walter 'Dutch'
Duering
Sent: Monday, May 23, 2011 8:03 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] A problem, a work-around and a question
Folks,
PRELIMINARY
In both PROBLEM and WORK-AROUND below the import procedure is stored as
a "Saved Imports". What happens as described in the PROBLEM and A
WORK-AROUND continues in the stored procedure.
THE PROBLEM
1. I use a Geographical Position System (GPS) unit that exports (via
intermediary software) to a comma delimited file.
2. One of the exported fields is "Duration", which is the length of time
that the GPS unit is held on station, a station being the point whose
geographical location is being measured.
3. Duration is in the format of: 00:00:00 (hours:minutes:seconds)
For my work a typical reading is: 00:01:30
4. When imported to an existing MS Access field Access insists on
importing it as a number in the form of a time of day (1:30 [AM]), even
when the import field is defined as text. This is highly undesirable for
the integrity of the data.
5. The import procedure is saved and later called either directly or by
code. It continues to import as time of day, not as elapsed time.
A WORK-AROUND
1. Before in importing the comma delimited file the first time, prefix
the true value with an alpha character, thus:
. 00:01:30 becomes a00:01:30
This has to happen for every record in the file, otherwise errors
occur and the same continue to occur when the stored procedure is executed.
2. Import the data using "Text File".
3. Save the import steps into "Saved Imports".
4. The data field "Duration" imports exactly as expected: "a00:01:30"
5. Using the saved the import procedures to "Saved Imports". With the
next file to be imported DO NOT change the input file to "a00:01:30",
use the 00:01:30 as exported by the GPS software.
The field imports accurately as a text field. In other words, the
saved import procedure is importing as expected when a non saved import
is screwing up the data.
THE QUESTION
No matter how I try to configure MS Office to disable how Office handles
date fields (or text fields that happen to be expressed in numeral
characters), it always comes back to the defaults as time or numbers.
Is there a way to completely turn off these 'automatic' actions?
This question also applies to LISTS.
Also, if anyone can answer this for OPENOFFICE, which is only marginally
better I'd be grateful.
Dutch
.
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar