Math Question

[Gary F Shelton]

My IT group has a date table. At first I didn't understand why we need the
table but now after I pulled some data out of our AS400 mainframe I
understand.

The data I pulled is Ship DT Centruy, Ship DT year, Ship DT month, Ship DT
Day... The format looks like this for a few records:
1 5 4 2 (The answer is 4/2/2005)

Now in the date table the record that ties back to 4/2/2005 is 1050402.

My questions are:
What math equation do I do to make sure that all records that have a Year of
one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a month
of one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a Day of
one digit will have a zero in front of them?

Such a pain ....

 

[John W. Vinson]

My IT group has a date table. At first I didn't understand why we need the
table but now after I pulled some data out of our AS400 mainframe I
understand.

The data I pulled is Ship DT Centruy, Ship DT year, Ship DT month, Ship DT
Day... The format looks like this for a few records:
1 5 4 2 (The answer is 4/2/2005)

Now in the date table the record that ties back to 4/2/2005 is 1050402.

My questions are:
What math equation do I do to make sure that all records that have a Year of
one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a month
of one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a Day of
one digit will have a zero in front of them?

Such a pain ....

What's the datatype of the date table field? If it's Text, you can use the
Format() function to concatenate the pieces:

[Ship DT Century] & Format([Ship DT Year], "00") & Format([Ship DT Month],
"00") & Format([Ship DT Day], "00")

This will give 0990515 for May 15 1999 - if you don't want the leading zero
then use

100*[Ship DT Century] + [Ship DT Year] & Format([Ship DT Month], "00") &
Format([Ship DT Day], "00")

 

[Tom van Stiphout]

On Sun, 16 Nov 2008 18:19:02 -0800, Gary F Shelton

Curious that 2005 is in the first century.

Format(x, "00")
will return x with a leading 0 if needed.

-Tom.
Microsoft Access MVP

 

[John Spencer]

Instead of math equations try the following (All on one line)

[Ship DT Century] & Format(DateSerial([Ship DT year], [Ship DT month], [Ship
DT Day]),"yymmdd")


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

 

[Gary F Shelton]

This equation worked like a charm!

100*[Ship DT Century] + [Ship DT Year] & Format([Ship DT Month], "00") &
Format([Ship DT Day], "00")

The records are returning vlaues that look like these examples 1081114,
1081027, 1081028. Now I have to match up the records with the table four
times as I have the Order input date, Scheculed Ship date, Requested Ship
Date, and Actual Ship date to convert. Also I need to make sure that I
exclude the records where someone fat keyed it wrong. I noticed a record were
someone put the month as zero. Obviously, this will be a problem but a
solveable one.

--
GS

Instead of math equations try the following (All on one line)

[Ship DT Century] & Format(DateSerial([Ship DT year], [Ship DT month], [Ship
DT Day]),"yymmdd")


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

My IT group has a date table. At first I didn't understand why we need the
table but now after I pulled some data out of our AS400 mainframe I
understand.

The data I pulled is Ship DT Centruy, Ship DT year, Ship DT month, Ship DT
Day... The format looks like this for a few records:
1 5 4 2 (The answer is 4/2/2005)

Now in the date table the record that ties back to 4/2/2005 is 1050402.

My questions are:
What math equation do I do to make sure that all records that have a Year of
one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a month
of one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a Day of
one digit will have a zero in front of them?

Such a pain ....

 

[Gary F Shelton]

Need help writing what I think is a IFF Is date statement. Some of the
records have errors where someone fat fingered the month as 00 or a day as
32. Obviously, this is not a good record so I think I will just exclude them.
Actually, I should try and have those records put in a seperate table so I
can eye ball them with their quantity to determine I just want to purge them
or not... any suggestions/help is greatly appreciated.
--
GS

This equation worked like a charm!

100*[Ship DT Century] + [Ship DT Year] & Format([Ship DT Month], "00") &
Format([Ship DT Day], "00")

The records are returning vlaues that look like these examples 1081114,
1081027, 1081028. Now I have to match up the records with the table four
times as I have the Order input date, Scheculed Ship date, Requested Ship
Date, and Actual Ship date to convert. Also I need to make sure that I
exclude the records where someone fat keyed it wrong. I noticed a record were
someone put the month as zero. Obviously, this will be a problem but a
solveable one.

--
GS

Instead of math equations try the following (All on one line)

[Ship DT Century] & Format(DateSerial([Ship DT year], [Ship DT month], [Ship
DT Day]),"yymmdd")


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

My IT group has a date table. At first I didn't understand why we need the
table but now after I pulled some data out of our AS400 mainframe I
understand.

The data I pulled is Ship DT Centruy, Ship DT year, Ship DT month, Ship DT
Day... The format looks like this for a few records:
1 5 4 2 (The answer is 4/2/2005)

Now in the date table the record that ties back to 4/2/2005 is 1050402.

My questions are:
What math equation do I do to make sure that all records that have a Year of
one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a month
of one digit will have a zero in front of them?
What math equation do I do to make sure that all records that have a Day of
one digit will have a zero in front of them?

Such a pain ....

 

[John W. Vinson]

Need help writing what I think is a IFF Is date statement. Some of the
records have errors where someone fat fingered the month as 00 or a day as
32. Obviously, this is not a good record so I think I will just exclude them.
Actually, I should try and have those records put in a seperate table so I
can eye ball them with their quantity to determine I just want to purge them
or not... any suggestions/help is greatly appreciated.

The IsDate() function works on a String, not on individual numbers - and
unfortunately for your case, the DateSerial function is TOO smart! It will
cheerfully accept and correctly translate Month 00 or Day 32 (e.g.
DateSerial(2008, 0, 32) will translate to #1/31/2008#).

Try

IIf(IsDate([ShipDTMonth] & "/" & [ShipDTDay] & "/" & Format([ShipDTYr], "00"),
<it's a date>, <it's an error>)

To generate a table with the erroneous records, just add the IsDate() function
call above as a calculated field in a Query (including whatever other fields
you need to see), and use a criterion of False on the calculated field.