Import AS/400 or DB2 into Access or Excel ** The Date Issue

[Gilbert Noetzel]

Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and Newsgroup
have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The C
is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

I need to convert or 'translate' the data into the following date
format: mmddyyyy (mm/dd/yyyy).

Can anyone help? I have something resolve close but it is not working
the way I want with the end result of mmddyyyy.

Here is what I got so far:

IIf(Len([CMDLPY])=7, Format(Mid([CMDLPY],4,2) & "-" & Mid([CMDLPY],6,2)
& "-" & Mid([CMDLPY],2,2),"Medium Date"), Format(Mid([CMDLPY],3,2) & "-"
& Mid([CMDLPY],5,2) & "-" & Left([CMDLPY],2),"Medium Date"))

End result is: 10/23/2006

The goal is to use either Access or Excel to Export the data into CVS
text file for a new system that requires the date format to be mmddyyyy

Thank you all for your help.

Gil

 

[Jason W. Martin]

You have the formula already but just need to replace Medium Date with
"MMDDYYYY". This is a custom format. Look at Access Help for the format
function and you'll see how to create custom formats.

-Jason

 

[Gilbert Noetzel]

Jason -

Thank you for solving my little problem.

I've been looking over the 'Bible Manual on Access 2003' and the Google
Search..and none had made any reference to the solution. It may seemed
obvious to most people..but I am just getting my feet wet on this Access
thing and there are not a lot of resources for 'examples' of codes or
refrenceing of ways to use the Query or other ways to format incoming or
outgoing data.

You made my job a lot easier now..that I have that big thorn out of my
thigh!

Thanks

Gil

You have the formula already but just need to replace Medium Date with
"MMDDYYYY". This is a custom format. Look at Access Help for the
format function and you'll see how to create custom formats.

-Jason

Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and Newsgroup
have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The C
is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

I need to convert or 'translate' the data into the following date
format: mmddyyyy (mm/dd/yyyy).

Can anyone help? I have something resolve close but it is not working
the way I want with the end result of mmddyyyy.

Here is what I got so far:

IIf(Len([CMDLPY])=7, Format(Mid([CMDLPY],4,2) & "-" &
Mid([CMDLPY],6,2) & "-" & Mid([CMDLPY],2,2),"Medium Date"),
Format(Mid([CMDLPY],3,2) & "-" & Mid([CMDLPY],5,2) & "-" &
Left([CMDLPY],2),"Medium Date"))

End result is: 10/23/2006

The goal is to use either Access or Excel to Export the data into CVS
text file for a new system that requires the date format to be mmddyyyy

Thank you all for your help.

Gil

 

[Gilbert Noetzel]

One more thing Folks -

Jason solution works..now about about those data that have no 'dates
data' in them..that the result should be 'blank' in the field, but it is
showing up ast --0 ... What can i do?

Gil

You have the formula already but just need to replace Medium Date with
"MMDDYYYY". This is a custom format. Look at Access Help for the
format function and you'll see how to create custom formats.

-Jason

Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and Newsgroup
have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The C
is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

I need to convert or 'translate' the data into the following date
format: mmddyyyy (mm/dd/yyyy).

Can anyone help? I have something resolve close but it is not working
the way I want with the end result of mmddyyyy.

Here is what I got so far:

IIf(Len([CMDLPY])=7, Format(Mid([CMDLPY],4,2) & "-" &
Mid([CMDLPY],6,2) & "-" & Mid([CMDLPY],2,2),"Medium Date"),
Format(Mid([CMDLPY],3,2) & "-" & Mid([CMDLPY],5,2) & "-" &
Left([CMDLPY],2),"Medium Date"))

End result is: 10/23/2006

The goal is to use either Access or Excel to Export the data into CVS
text file for a new system that requires the date format to be mmddyyyy

Thank you all for your help.

Gil

 

[Rick Brandt]

Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and Newsgroup
have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The C
is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

Just a mild correction here. Your AS400 has a numeric or text field that
happens to be used to store numbers representing a date. If that were an
actual LDate type field then Access would have no problems working with it
as a real date and formatting it any way you like.

 

[Jason W. Martin]

The field in Access is a Number data type. Do you have an Append query
that's linked via ODBC to the AS400 data and writing to a table in Access
then exporting from that table? If so, your date field is a Number which
has a default setting of 0 - just remove the default.

Also, you may be able to use an IIF statement such as
IIF([CMDLPY]=0,"",<false part>)

- Jason

One more thing Folks -

Jason solution works..now about about those data that have no 'dates data'
in them..that the result should be 'blank' in the field, but it is showing
up ast --0 ... What can i do?

Gil

You have the formula already but just need to replace Medium Date with
"MMDDYYYY". This is a custom format. Look at Access Help for the format
function and you'll see how to create custom formats.

-Jason

Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and Newsgroup
have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The C
is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

I need to convert or 'translate' the data into the following date
format: mmddyyyy (mm/dd/yyyy).

Can anyone help? I have something resolve close but it is not working
the way I want with the end result of mmddyyyy.

Here is what I got so far:

IIf(Len([CMDLPY])=7, Format(Mid([CMDLPY],4,2) & "-" & Mid([CMDLPY],6,2)
& "-" & Mid([CMDLPY],2,2),"Medium Date"), Format(Mid([CMDLPY],3,2) & "-"
& Mid([CMDLPY],5,2) & "-" & Left([CMDLPY],2),"Medium Date"))

End result is: 10/23/2006

The goal is to use either Access or Excel to Export the data into CVS
text file for a new system that requires the date format to be mmddyyyy

Thank you all for your help.

Gil

 

[Jason W. Martin]

This is typical of apps written in DB2 on a 400. Years ago, there wasn't a
date type. It's also typical to see a number 1 as the first digit added to
numbers to make them "Year 2000 Compliant". I've got several functions
written to handle that I use over and over.

- Jason

 

[Gil]

Jason -

I see what you mean..so if the below example is correctly stated..then
it should work:

Date of First Purchase:
IIf([CMDFPO]=0,"",IIf(Len([CMDFPO])=7,Format(Mid([CMDFPO],4,2) & "-" &
Mid([CMDFPO],6,2) & "-" &
Mid([CMDFPO],2,2),"mmddyyyy",0),Format(Mid([CMDFPO],3,2) & "-" &
Mid([CMDFPO],5,2) & "-" & Left([CMDFPO],2),"mmddyyyy",0)))

And guess what!?! It worked! I copy paste this to the Access Build box
and it work perfectly! Gosh Darn I am very Stupid!

Thank you again Jason and veryone else!

Gil

The field in Access is a Number data type. Do you have an Append query
that's linked via ODBC to the AS400 data and writing to a table in
Access then exporting from that table? If so, your date field is a
Number which has a default setting of 0 - just remove the default.

Also, you may be able to use an IIF statement such as
IIF([CMDLPY]=0,"",<false part>)

- Jason

One more thing Folks -

Jason solution works..now about about those data that have no 'dates
data' in them..that the result should be 'blank' in the field, but it
is showing up ast --0 ... What can i do?

Gil

You have the formula already but just need to replace Medium Date
with "MMDDYYYY". This is a custom format. Look at Access Help for
the format function and you'll see how to create custom formats.

-Jason


Hey guys & gals (if any) this is my first posting to the group.

Got a nagging issue that I am trying to resolve. Google and
Newsgroup have not posted anything that can resolve what I need to do.

I have an AS/400 system here that has a date format of CYYMMDD. The
C is for Century Code and below is the example:

1061023 = 2006/10/23 = October 23, 2006

I need to convert or 'translate' the data into the following date
format: mmddyyyy (mm/dd/yyyy).

Can anyone help? I have something resolve close but it is not
working the way I want with the end result of mmddyyyy.

Here is what I got so far:

IIf(Len([CMDLPY])=7, Format(Mid([CMDLPY],4,2) & "-" &
Mid([CMDLPY],6,2) & "-" & Mid([CMDLPY],2,2),"Medium Date"),
Format(Mid([CMDLPY],3,2) & "-" & Mid([CMDLPY],5,2) & "-" &
Left([CMDLPY],2),"Medium Date"))

End result is: 10/23/2006

The goal is to use either Access or Excel to Export the data into
CVS text file for a new system that requires the date format to be
mmddyyyy

Thank you all for your help.

Gil