Converting Number to Date

[Guest]

It seems from my research that I cannot simply use an expression to convert a
date such as 20060831 in an excel file from a number to a date format in
Access without first converting the number to a string then breaking out the
different componenets of the date Year, Month and Day then somehow
recombining them back into one field and somehow formatting them to a date
format. If so I need some help.

My thought is, for each componenet, to do something like this:

1) Make a query that imports all of the fields needed for my table and three
additional fields that include 3 seperate expressions using Left, Mid and
Right breaking out each componenent of the date (Year, Month and Day). Not
sure how to convert numbers to strings.

Putting these three fields back together is also foggy. Questions I have are:

"RequestDate" is the name of the field in my Access table and "Request Date"
is the name of the field in my Excel file.

A) Do I create another query to join the three fields making a new
RequestDate field?
B) If so in the query, how do I put them together? Concastenate?
C) Once converted to string, how do I change the format to Date?

Thanks,

Dennis

 

[Guest]

MyDate = DateSerial(Left(strDate,4), Mid(strDate,5,2), Right(strDate,7,2))

 

[Albert D. Kallal]

It seems from my research that I cannot simply use an expression to
convert a
date such as 20060831 in an excel file from a number to a date format in
Access without first converting the number to a string then breaking out
the
different componenets of the date Year, Month and Day then somehow
recombining them back into one field and somehow formatting them to a date
format. If so I need some help.

Hum, sure, you can use a expression.....

dateserial(left([mydatefield],4),mid([mydatefield],5,2),right([mydatefield],2)

Get the above expression working, and then simply add a new field to your
table, and run a update query to move the text values to a actual date
field.

 

[Marshall Barton]

It seems from my research that I cannot simply use an expression to convert a
date such as 20060831 in an excel file from a number to a date format in
Access without first converting the number to a string then breaking out the
different componenets of the date Year, Month and Day then somehow
recombining them back into one field and somehow formatting them to a date
format. If so I need some help.

My thought is, for each componenet, to do something like this:

1) Make a query that imports all of the fields needed for my table and three
additional fields that include 3 seperate expressions using Left, Mid and
Right breaking out each componenent of the date (Year, Month and Day). Not
sure how to convert numbers to strings.

Putting these three fields back together is also foggy. Questions I have are:

"RequestDate" is the name of the field in my Access table and "Request Date"
is the name of the field in my Excel file.

A) Do I create another query to join the three fields making a new
RequestDate field?
B) If so in the query, how do I put them together? Concastenate?
C) Once converted to string, how do I change the format to Date?

Just use a calculated field in the query that does the
import:

RequestDate: CDate("#" & Left([Request Date],4) & "-" &
Mid([Request Date],5,2) & "-" & Right([Request Date],2) &
"#")