access 2003 data convert

[Syrch]

The company i work for uses the as400 and for one of the tables im using the data for dates is set as a number and in format century/yy/mm/dd. I have no idea why they included the century honestly but when I link the table in access I obviously have to use that format to pull certain dates. Well I need to use Month: Format([DATE],"mm/yy") in my date field which will break everything down by month. Unfortunately i can't do that with the date formatted the way it is. I want to convert the dates from numbers to text and i know i need to make a Make Table and then under the properties of that table I can edit that column to text but how do I remove the century? Any help on this would be greatly appreciated.

 

[KLin]

so for example, today's date would be stored as 20070822 correct? Use the following to make the as400 date field an actual date in your access table:

Cdate(mid([As400DateField], 5, 2) & "/" & mid([As400DateField, 7, 2) 7 "/" & Right([AS400DateField], 4))

 

[Syrch]

close but no. Todays day would be down as 1070822 (8/22/07-todays date). I but the as400 is importing it as a number not a date and i can't do much with it like that. Do do what you are thinking i believe it would be mid([Date],4,2)&"/"&right([Date],2)&"/"&mid([Date],2,2) but that won't allow me to break it down by month after that in my overall query.

 

[KLin]

Originally posted by: Syrch
close but no. Todays day would be down as 1070822 (8/22/07-todays date). I but the as400 is importing it as a number not a date and i can't do much with it like that. Do do what you are thinking i believe it would be mid([Date],4,2)&"/"&right([Date],2)&"/"&mid([Date],2,2) but that won't allow me to break it down by month after that in my overall query.

Try this

Cdate(mid([As400DateField], 5, 2) & "/" & mid([As400DateField, 7, 2) 7 "/20" & mid([AS400DateField], 2, 2))

That should return 08/22/2007. You could then use the Month function to just return the month from the date.

 

[Syrch]

I believe with either formula i would need to create a query with my desired fields (including date but use the formula for my date field) then turn that into a make table and append it to my original linked table in order to keep it updated but would that work?

Im making the make table now and the table is going to be big enough that i'll just leave it until i come in tomorrow. So if it doesn't sound like it would work i'll end it.

 

[KLin]

Originally posted by: Syrch
I believe with either formula i would need to create a query with my desired fields (including date but use the formula for my date field) then turn that into a make table and append it to my original linked table in order to keep it updated but would that work?

Im making the make table now and the table is going to be big enough that i'll just leave it until i come in tomorrow. So if it doesn't sound like it would work i'll end it.

You don't need to do that. Just reference the formula in a query against the newly created table, and you should be able to group by month.