I really need some help here.

I'ce created a perl script that collect a lot of data for me and stores in a
tab delimitered txt file that I want to import to my database. The problem
is Access won't accept my data due to type conversion failure. All my time
info is stored as "Jul 27 21:31" and I need to know what I can do to get
Access to accept that and let me import all my data.

Any help and suggestions would be wellcome.

Hi Thomas. You could import the date value into a field of type Text. This
could be in a temporary import table. Once it is there, you could convert it
into a Date value by using either an intrinsic conversion function or a
function you've written yourself in VBA. Either type of function could be
called in an update query, so you could zip through your newly-imported rows
and update them quickly.

The problem is that the import feature is not smart enough to convert the date
values in the format they're starting from. Import them as text and then
manipulate them.

Hope this helps.

Karl

----------------------------------------------
Karl Hoaglund, MCSD
Microsoft Access Programmer
http://www.nexuscgi.net

Thomas Andersson wrote:

I did not get an error like you descibed that but it may be due to me
manually doing a simple File/GetExternalData/Import and importing the
data from a text file. The result I get for a date field like yours is
#Num in the column. However, if I change the text file to alsoinclude a
year it imports fine. So if you wrote a script that can get the month,
day, hours, minutes could you now also add a year? If you can't, Karl's
advice is spot on, IMO.

If you import the data into a work table and import the date as a string, you
can then manipulate it to a date with

IIF(IsDate([TheStringField]),CDate([TheStringField]),Null)

CDate("Jul 27 21:31") returns the following date 7/27/2010 9:31:00 PM

The year will automatically default to the current year (assuming your system
date is correctly set).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Salad wrote:

Salad wrote:

Adding a year is simple so I'll try that (just concatenate it in front of
the variable).
One related question. My DB also ahve auto number and to let it be generated
by DB I can select to skip that column during inport. Q though is. Should I
leave the import rows as is (starting with the first non auto data) or
insert a empty tab at start of each row so column numbers match?

Thomas Andersson wrote:
I had 2 cols. One col might have looked like
08/05/2010 11:;30
and the other
Aug 5 2010 11:30
and both appended fine. Not sure what you mean "in front of variable".

that I was manually doing it. I ran a query to export my results using
Docmd.TransferText acExport....
So I could have used it as well to import, changing acExport to
acImport. I recommend saving to a file specification for importing the
specified layout.

I don't understand "inserting an empty tab". If you need a flag field,
create a flag field.

On Aug 4, 10:03*pm, "Thomas Andersson" <tho... (AT) tifozi (DOT) net> wrote:

CVDate("Jul 27 " & Year(Now()) & " 21:31")
works.

Assuming your date is in a text field named "Field3" you would do:

CVDate(Left([Field3],7) & Year(Now()) & Mid([Field3],8)

in your query and that (should) form a proper date for Access to
import. (You might have to surround it with #'s though.)