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


"goldie40" <(E-Mail Removed)> wrote in message
news:0A03AA0B-C9D5-4078-8A66-(E-Mail Removed)...
> 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.

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?


"Jeff Boyce" wrote:

> 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
>
>
> "goldie40" <(E-Mail Removed)> wrote in message
> news:0A03AA0B-C9D5-4078-8A66-(E-Mail Removed)...
> > 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'm not sure what "problem" you're solving...

What happens when you try the approach I offered?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"goldie40" <(E-Mail Removed)> wrote in message
news1E201B3-BF66-466D-ABD8-(E-Mail Removed)...
> 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?
>
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>>
>> "goldie40" <(E-Mail Removed)> wrote in message
>> news:0A03AA0B-C9D5-4078-8A66-(E-Mail Removed)...
>> > 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.
>>
>>
>>

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
'====================================================


goldie40 wrote:
> 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?
>
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>>
>> "goldie40" <(E-Mail Removed)> wrote in message
>> news:0A03AA0B-C9D5-4078-8A66-(E-Mail Removed)...
>>> 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.
>>
>>

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" wrote:

> 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
> '====================================================
>
>
> goldie40 wrote:
> > 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?
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> 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
> >>
> >>
> >> "goldie40" <(E-Mail Removed)> wrote in message
> >> news:0A03AA0B-C9D5-4078-8A66-(E-Mail Removed)...
> >>> 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.
> >>
> >>
>