Combining Cdate with Iif in calculated query

[lefty38]

this is a great board for answers

How would I incorporate CDate into the below query string in order to calculate the “stringed” date value ?

 Status: IIf( ([Status Cd]<>"COMP",[Status Cd],IIf([expires]< Date(),"Pass Due",IIf([Expires]-Date()<=30,"30 Days",
IIf([Expires]-Date()<=60,"60 Days","IIf([Expires]-Date()<=90,"90 Days","Planned"))))

The “Status column” is a text field
Values are
COMP
Date values from any date calculated from Date()

Resulting into a new column or the existing column
Output desired in the same / new column

Comp
Pass Due
30 Days
60 Days
90 Days
Planned

and of course, thank you

 

[xenou]

Normally datevalue() but that can cause problems depending on the type of strings that might be in the field. Is [expires] a date field or a string field? If a string field, what values can it hold besides dates? From your post description I'd say you are using dates in the [status] field but your actual code uses dates in the [expires] field, so it's unclear what's going on.

 

[lefty38]

[PHP messed up the original code

should have been Status field which is a string field

but I cant get the full query statement to post

"Status: IIf([Status]="COMP",[Status],IIf([Status]
<Date(),"R",IIf([Status]-Date()<=30,"30 Days",
IIf([Status]-Date()<=60,"60 Days","90 Days"))))"

 

[lefty38]

Let me re-ask the question

How would I incorporate CDate into the below query string in order to calculate the “stringed” date value ?

Currently Status is a "text field' in order to capture the text COMP and date strings

[SIZE=1]Status: IIf( ([Status]="COMP","Comp",
IIf([Status]< Date(),"Pass Due",
IIf([Status]-Date()<=30,"30 Days",
IIf([Status]-Date()<=60,"60 Days",
IIf([Status]-Date()<=90,"90 Days","Planned"))))





[/SIZE]

The “Status column” is a text field
Values are
COMP
along with Date values

Resulting into a new column or the existing column
Output desired in the same / new column
with the new calculated values

Comp
Pass Due
30 Days
60 Days
90 Days
Planned

and of course, thank you

 

[xenou]

This seems to work:

SELECT IIF(IsDate(Status),Switch(DateValue(Status)<Date(),"Pass Due",DateValue(Status)-Date()<=30,"30 Days",DateValue(Status)-Date()<=60,"60 Days",DateValue(Status)-Date()<=90,"90 Days",1=1,"Planned"),"COMP") as Status
FROM Table11;

The above assumes everything not a date is "COMP" (since that is the only value besides dates).
If you want to guarantee that the value is COMP and not some other invalid value such as COPM then:

SELECT IIF(IsDate(Status),Switch(DateValue(Status)<Date(),"Pass Due",DateValue(Status)-Date()<=30,"30 Days",DateValue(Status)-Date()<=60,"60 Days",DateValue(Status)-Date()<=90,"90 Days",1=1,"Planned"),IIF(Status="COMP","COMP","ERROR")) as Status
FROM Table11;

 

[lefty38]

xnou your 2nd code will work great for the immediate need -- thank you

just curious how would I insert other "date criteria's" into the code?

Chart_Status: IIf(IsDate([Status]),Switch(DateValue([Status])<Date(),"Pass Due",
DateValue([Status])-Date()<=30,"30 Days",
DateValue([Status])-Date()<=60,"60 Days",
DateValue([Status])-Date()<=90,"90 Days",
DateValue([Status])-Date()<=120,"120 Days",1=1,"Planned"),IIf([Status]="COMP","COMP","ERROR"))

Never mind -- I got it to work

thank you again