Re: Converting Number to Date
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Fri, 01 Sep 2006 11:55:10 -0500
Don wrote:
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) &
"#")
--
Marsh
MVP [MS Access]
.
- Prev by Date: Re: How to enforce security on hiddent objects?
- Next by Date: Re: Object Set?
- Previous by thread: Re: Converting Number to Date
- Next by thread: Re: How to enforce security on hiddent objects?
- Index(es):
Relevant Pages
|
