Text import - date format

[Mohan]

Hi
I have a text file which I import into Access table using transfertext method.
everything is okay except few date columns are in ddmmyyyy format

In some of the columns I have a date in the follwoing format ddmmmyyyy (i.e
13Oct2007) - without any slash or hyphen. How can I get this in proper data
format 10/13/2008 (mm/dd/yyyy) either at the time of importing or after
importing get this proper date in a different column.

Aslo, I have some dollar amount columns and signage (negative or positve) is
given in a separate column. So all the dollar amounts are abosolute value.
Based on the signage, I have to multiply by -1 if negative. I don't think
this can be done at the time of importing (using transfer text). How can I
update the dollar amount in a new column with the signage?

Thanks
Mohan

 

[Pat Hartman]

It is not possible to handle inconsistent formatting when using the standard
transfer methods. You would need to write custom VBA code. After you
import the data, add two new columns to the table, one defined as date and
the other defined as currency. Create an update query to copy the string
values to the new fields.

cDate(Left(indate,2) & "/" & Mid(indate,3,3) & "/" & Right(indate,4)) as
FormattedDate

IIf(sign = "-", inAmt * -1, inAmt) As SignedAmt