Converting 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

 

[ETID]

My experience with AS400 date format is that it is in CCYYMMDD format ...."20030701"

IE: in MSAccess
=Format(Year(Date()),"0000&quot & Format(Month(Date()),"00&quot & Format(Day(Date()),"00&quot

in MSExcel
=text(year(now()),"0000&quot&text(month(now()),"00&quot&text(day(now()),"00&quot

 

[rjd2003]

My problem is that there are 3 fields. I am now trying to make subqueries in AS400 to get rid of "zero" fields.

 

[ETID]

my_date:=[LKINGD] & "/" & [LKINGM]& "/" & [LKINGJ]