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
http://I.Am/DougSteele
(no private e-mails, please)
"Jerry Kinder" <(E-Mail Removed)> wrote in message
news:ug2h$(E-Mail Removed)...
> 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
>
>
> "Ken Snell (MVP)" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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>
>>
>> "Jerry Kinder" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >
>>
>>
>
>
Similar Threads
