Converting 4 digit numbers to a date

[Kellen]

I have a query to create in access based off a query already created in SQL

The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)

OPDT is a digit number in text format. The function converts 9606 into 1996-06-01

What is the access method in order to this?

Thanks in advance.

 

[pbaldy]

The equivalent of CAST is a conversion function, in this case CDate(). You can use the IIf() function in place of the CASE statement:

IIf(TestHere, WhatToDoIfTrue, WhatToDoIfFalse)

 

[Kellen]

I understand that but this is complex. Tried to do this using those and Access gave an error and highlighted END.

My code attempt:
OPENDATE: CDate(IIf(OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, RIGHT (OPDT, 2), LEFT (OPDT, 2) END END + '/' + '01' + '/' + IIf(LEFT (OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, LEFT(OPDT,2), RIGHT(OPDT, 2))

 

[pbaldy]

The word END is not used in an IIf(); the closing parentheses are what "end" it.

 

[Kellen]

The word END is not used in an IIf(); the closing parentheses are what "end" it.

Okay. But I still get errors. I decided to drop the date conversion until after I get the IIf statement working.

My code is :
IIf(OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, RIGHT (OPDT, 2), LEFT (OPDT, 2) + '/' + '01' + '/' + IIf(LEFT (OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, LEFT(OPDT,2), RIGHT(OPDT, 2))

Access tells me that the first comma is invalid, but that's need for an IIF statement though.

 

[pbaldy]

You forgot the Left formula there:

IIf(Left(OPDT, 2) > 12

I don't know if it will cause an error, but I'd get rid of the spaces between functions and parentheses. In other words, not:

Left (

but

Left(

 

[Kellen]

I figured it out. It needed quotes because it's a text. Thanks for your help.

 

[pbaldy]

No problem!