numbers as dates

  • Thread starter Thread starter sandie
  • Start date Start date
S

sandie

I have a database I link to that stores dates as an 8
digit numberic field (yyyyddmm) and I need to get that
into an actual date that I can work with in a Query. I
can do it in Excel with a =date formula but I can't seem
to get in in Access. Any help is appreciated
 
If this is a numeric field then you can use something like:
=DateSerial( Val( Left( Str([YourField]),4) ),Val( Mid(
Str([YourField]),5,2) ) ,Val( Left( Str([YourField]),2) ) )
 
sandie said:
I have a database I link to that stores dates as an 8
digit numberic field (yyyyddmm) and I need to get that
into an actual date that I can work with in a Query. I
can do it in Excel with a =date formula but I can't seem
to get in in Access. Any help is appreciated
Click to expand...

Sandie,
If the format of that number field is always yyyyddmm, then you can use:

NumberToDate:DateSerial(Left([Numberfield],4),Right([NumberField],2),Mid([NumberField],5,2))

Look up DateSerial() in VBA Help.
 
-----Original Message-----
sandie said:
I have a database I link to that stores dates as an 8
digit numberic field (yyyyddmm) and I need to get that
into an actual date that I can work with in a Query. I
can do it in Excel with a =date formula but I can't seem
to get in in Access. Any help is appreciated
Click to expand...

Sandie,
If the format of that number field is always yyyyddmm, then you can use:

NumberToDate:DateSerial(Left([Numberfield],4),Right ([NumberField],2),Mid([NumberField],5,2))

Look up DateSerial() in VBA Help.

--
Fred
Please reply only to this newsgroup.
I do not reply to personal email.
.
Thanks for the help! Sandie
Click to expand...
 
Back
Top