dates in table 2 fields need to be one field I can sort by date on

  • Thread starter Thread starter goldie40
  • Start date Start date
G

goldie40

aHere's the problem data is coming from a very old legacy system! The date in
the import table is actually 2 fields one storing month/day no leading zero
example: january 15 = 115, one storing the 4 pos year example='2007' I need
to combine these in do a field a can sort by date on and display as a date.
I've tried format not sure I had the string correct, I can combine them, and
display as text but can't seem to get them reconized as a date for sorting
and displaying. When I try formatting as day I getting really funky stuff
displayed/ Any help would be appreciated, thanks in advance.
 
I'll assume that what you have now is text, not numeric.

Take a look at the Right() function as a way to get the day-of-month value.

Anything left after that is removed must be the number of the month.

And you already have a "four-digit" year.

Now take a look at the DateSerial() function syntax in Access HELP. You can
use it to create an Access Date/Time value.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff, thanks for the reply, The data in the table coming down from the
transfer is numeric. This is really ugly, as there are more problems with the
file then just the date but thought I would start there! Do your suggestions
still apply? I'm sorta new to access would I use the query option over the
download table to create the new fields, then concat together?
 
I'm not sure what "problem" you're solving...

What happens when you try the approach I offered?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
To make those two numeric fields into a real date you might try the
DateSerial function with a little math.

DateSerial([YearField],[MonthDay]\100,[MonthDay] Mod 100)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John and Jeff,
Thanks for your help the date serial in an update query took care of the
problem.
Greatly appreciate you taking your time to assist.

John Spencer said:
To make those two numeric fields into a real date you might try the
DateSerial function with a little math.

DateSerial([YearField],[MonthDay]\100,[MonthDay] Mod 100)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Jeff, thanks for the reply, The data in the table coming down from the
transfer is numeric. This is really ugly, as there are more problems with the
file then just the date but thought I would start there! Do your suggestions
still apply? I'm sorta new to access would I use the query option over the
download table to create the new fields, then concat together?
Click to expand...
Click to expand...
 
Back
Top