Date Problem - Importing Text File

[Dave]

When importing a file with dates such as 23Jun2003 the
dates come in as text files. I cannot covert them to
date files in design view. I change the data type field
from text to date/time. I try different formats: ex
custom ddmmmyyyy but data is deleted. How do I import
the file and have the dates as date/time fields instead
of as text or having them deleted in conversion.

 

[Guest]

Hi Dave,

there is a similar post a few items below from Jenny.

Answer from Rick Brandt :into a temp table with all fields as text and then use an append query to move
the data to its final destination. The append query can utilize whatever
expressions and functions required to properly convert the data to its final
DataType.

In your case I couldn't find a function that would do the conversion directly
from your example string, but if you use Left(), Mid(), and Right() to add
spaces then CDate() would work.

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3) & " " &
Right([YourField], 4))
<<

 

[GaryW]

What do the threes represent in the Mid example string
below? "3, 3" Thanks in advance

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3)
& " " & Right([YourField], 4))




-----Original Message-----

Hi Dave,

there is a similar post a few items below from Jenny.

Answer from Rick Brandt : it is best to just import
into a temp table with all fields as text and then use an append query to move
the data to its final destination. The append query can utilize whatever
expressions and functions required to properly convert the data to its final
DataType.

In your case I couldn't find a function that would do the conversion directly
from your example string, but if you use Left(), Mid(), and Right() to add
spaces then CDate() would work.

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3) & " " &
Right([YourField], 4))
<<
--
HTH
Bernd

.

 

[Douglas J. Steele]

Mid([YourField], 3, 3) means take 3 characters from [YourField], start at
character 3. In other words, it'll take characters 3, 4 and 5 from the
string.

Since I don't see anything in this thread in terms of what [YourField]
contains, I can't comment on whether this is what you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What do the threes represent in the Mid example string
below? "3, 3" Thanks in advance

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3)
& " " & Right([YourField], 4))




-----Original Message-----

Hi Dave,

there is a similar post a few items below from Jenny.

Answer from Rick Brandt :

Generally anytime you need to "massage" imported data

it is best to just import
into a temp table with all fields as text and then use an append query to move
the data to its final destination. The append query can utilize whatever
expressions and functions required to properly convert the data to its final
DataType.

In your case I couldn't find a function that would do the conversion directly
from your example string, but if you use Left(), Mid(), and Right() to add
spaces then CDate() would work.

CDate(Left([YourField], 2) & " " & Mid([YourField], 3, 3) & " " &
Right([YourField], 4))
<<
--
HTH
Bernd

.