changing data types

[Dabbler]

Hi,
I have an external link to a text file. A date field is
in this format: 20040215

If during the link process I set the field as date/time, I
get an error in the resulting table, something like #num!

If I set it as TEXT, then it appears in the table, but I
am stuck with it as text type. Since it is a linked
table, I cannot change the data type after the fact.

So how do I use this field in a date-friendly manner in
queries and reports? Is there a way to programmatically
get this into another field or table as a date?

Thanks

 

[Joe Fallon]

I personally do not like linking to non-database files.
I import them.

But the idea would be the same because during the import (to a temp table)
you would need to keep that field as TEXT so that it comes in correctly.

Then you write an append query to move the data to the real table.
The query uses format or expressions to massage the raw data and append it
correctly.

So your queries should break up that text value into its Date equivalent.
Use DateSerial function and ocmbine each piece of the Text value into the
correct parameter.

 

[Van T. Dinh]

You can use the expression:

DateSerial( CInt(Left([DateText], 4)), _
CInt(Mid([DateText], 5, 2)), _
CInt(Right([DateText], 2)) )

to convert your DateText to a DateTime value.