Coverting date from AS400 query to MS Access

[rjd2003]

Hello from Holland!

I try to convert a date from AS400 query to MS Access through an ODBC.

Fields are as follows: LKINGD (day) LKINGM (month) and LKINGJ (year).

The desired result is: dd/mm/yy or dd-mm-yy.

I use the following formula in AS400 query:

date(digits(LKINGD)||'/'||digits(LKINGM)||'/'||digits(LKINGJ))

Running the query does not work.

How come?

By using the following formula:

digits(LKINGD)||digits(LKINGM)||digits(LKINGJ)

I succeed in transferring to MS Acces but the outcome is kind of strange: "F0F1F1" instead of "011190" that I expected.

Who can help me??

Thanks in advance,

Robert

 

[genomon]

Not sure about the AS400 code, but F0F1F1 is how IBM stores "011" in hex, which would take up the six spaces used by the decimal "011190". What I am sure about is that Access likes all its dates to be enclosed within pound signs like this: #variable#, where the variable is dimmed as a date datatype; also check out the format function in Access. Hope this helps a little.....
-Geno

 

[syates]

I used this in AS400 query and the result when linked to MS ACCESS produced a readable date. Hope this helps.


Field Expression
DATE 08212003

DATE2 digits(date)

DATEOPEN substr(date2,1,2)||'/'||substr(date2, 3,2)'/'||substr(date2,5,4)

DATE3 date(dateopen)

 

[rjd2003]

Thanks Syates,

It works except in those cases where the date is blank. AS400 seems not to be able to handle a blank date. I work with ending dates of contracts. Contracts that are still running have a day 0, month 0 and year 0.

Best regards,

RJ