>>>I cannot get this Access IIF to convert right. Could use some help.
>>>
>>>Here's the source
>>>
>>>IIf([PK_RQ]![RQ_STATUS]="C" Or [PK_RQ]![RQ_QTY_ISS]>=[PK_RQ]![RQ_QTY_REQ],IIf([PK_RQ]![RQ_DATE_PROM]>=Date()-
>>>365 Or [PK_RQ]![RQ_DATE_REQ]>=Date()-365,True,False),True)
>>>
>>>
>>>Here's what I have
>>>
>>>(CASE WHEN (RQ.RQ_STATUS = 'C' OR RQ.RQ_QTY_ISS >= RQ.RQ_QTY_REQ) AND
>>>(RQ.RQ_DATE_PROM >= DATEADD(dd,0,GETDATE())-365 OR RQ.RQ_DATE_REQ >= DATEADD(dd,0,GETDATE())-365) THEN 1 END = 1) AND
>>>
>>
>>It looks like that got chopped off.
>
>This CASE is part of a WHERE clause. The rest of the WHERE clause is Ok. This part is the offending section.
>
>
>
>>
>>Borislav answered a very similar question the other day, using CASE in a much more readable format. The formatting above practically defies you to follow it.
Try
(CASE WHEN (RQ.RQ_STATUS = 'C' OR RQ.RQ_QTY_ISS >= RQ.RQ_QTY_REQ) AND
>(RQ.RQ_DATE_PROM >= DATEADD(dd,0,GETDATE())-365 OR RQ.RQ_DATE_REQ >= DATEADD(dd,0,GETDATE())-365) THEN 1
ELSE 1 END )
Another tip. In the SQL profiler you can capture the SQL that is executed against the database. You can copy and paste that in the Quary Analyzer and run it. This will give you a more detailed message of what is wrong.
Walter,