dates in access

[Jerry Kinder]

Help I am lost!

I imported a csv file into Excel then into Access with one column of dates.
In the csv file the dates are just numbers, I am assuming they are cereal
numbers for the dates?? How do I get them back to dates in access? I
imported first to Excel and tried to format them as dates but they remain
numbers. Then imported them to Access. In access I can not get them to
convert to dates either. LOST.

Thanks,
Jerry

 

[Ken Snell \(MVP\)]

If the numbers are five-digit integers, then they probably are the numeric
representation of the date.

You can convert them to the "date" by using this expression:

CDate([FieldName])

where FieldName is the name of the field that holds the numbers.

You can use this expression in a query that appends the data to another
table, or you can use it to update a date field in a tale via an update
query. Tell us more about what you want to do with the date when you get it
converted.

 

[Jerry Kinder]

Hi Ken,
There are eight digits - 11302006 - in each cell of the column. It is the
"Renew Date" for a club roster. It would be helpful to know when each
member last renewed their membership. What do you think this number might
be if not a date? The Col Hedder is "RenewDate"

How do I do an update query? - "you can use it to update a date field in a
table via an update query".

Thanks, Jerry

If the numbers are five-digit integers, then they probably are the numeric
representation of the date.

You can convert them to the "date" by using this expression:

CDate([FieldName])

where FieldName is the name of the field that holds the numbers.

You can use this expression in a query that appends the data to another
table, or you can use it to update a date field in a tale via an update
query. Tell us more about what you want to do with the date when you get it
converted.

--

Ken Snell
<MS ACCESS MVP>

Help I am lost!

I imported a csv file into Excel then into Access with one column of
dates.
In the csv file the dates are just numbers, I am assuming they are cereal
numbers for the dates?? How do I get them back to dates in access? I
imported first to Excel and tried to format them as dates but they remain
numbers. Then imported them to Access. In access I can not get them to
convert to dates either. LOST.

Thanks,
Jerry

 

[Douglas J. Steele]

If I had to guess, I'd say that was 30 Nov, 2006. Sure looks like mmddyyyy
to me!

Assuming I'm correct, try:

Function ConvertToDate(IntegerDate As Long) As Date

Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer

intMonth = IntegerDate \ 1000000
intDay = (IntegerDate Mod 1000000) \ 10000
intYear = IntegerDate - (intMonth * 1000000&) - (intDay * 10000&)

ConvertToDate = DateSerial(intYear, intMonth, intDay)

End Function

If you're positive that every user will have their Short Date format set to
mm/dd/yyyy through Regional Settings, you could use the following instead:

CDate(Format(IntegerDate), "00\/00\/0000"))

(but I'd recommend against making that assumption!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Ken,
There are eight digits - 11302006 - in each cell of the column. It is the
"Renew Date" for a club roster. It would be helpful to know when each
member last renewed their membership. What do you think this number might
be if not a date? The Col Hedder is "RenewDate"

How do I do an update query? - "you can use it to update a date field in a
table via an update query".

Thanks, Jerry

If the numbers are five-digit integers, then they probably are the
numeric
representation of the date.

You can convert them to the "date" by using this expression:

CDate([FieldName])

where FieldName is the name of the field that holds the numbers.

You can use this expression in a query that appends the data to another
table, or you can use it to update a date field in a tale via an update
query. Tell us more about what you want to do with the date when you get it
converted.

--

Ken Snell
<MS ACCESS MVP>

Help I am lost!

I imported a csv file into Excel then into Access with one column of
dates.
In the csv file the dates are just numbers, I am assuming they are cereal
numbers for the dates?? How do I get them back to dates in access? I
imported first to Excel and tried to format them as dates but they remain
numbers. Then imported them to Access. In access I can not get them to
convert to dates either. LOST.

Thanks,
Jerry