Access Syntax Error (Missing Operator)

[toilettenseife]

Access is giving me a syntax error on this section of code. I keep looking at it and I can't see what's wrong. Any ideas?

IIf([Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null,'Supplier Receipt Date',
IIf([Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null And [Closed Order File].[SUPPLIER_RECEIPT_DATE] Is Null, 'Logistics P/U Date',
IIf([Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null And [Closed Order File].[SUPPLIER_RECEIPT_DATE] Is Null And
[Closed Order File].[LOGISTICS P/U DATE_CS] Is Null,'Customer Ship Date',
IIf([Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null And [Closed Order File].[SUPPLIER_RECEIPT_DATE] Is Null And
[Closed Order File].[LOGISTICS P/U DATE_CS] Is Null And [Closed Order File].[CUSTOMER_SHIPMENT_DATE] Is Null,'PO/RO Create Date',
'Logistics Delivery Date')))) AS Receipt_Date_Used,

 

[Joe4]

The syntax looks fine to me, provided that all your field references are correct and are available.
How do you know it is this part that is causing issues?

Usually, if I mind a problem like this, I will proceed by doing the following:
- Remove the calculated field from the query entirely, and see if it works.
- If it does, then rebuild the calculated expression one IIF at a time (just trying doing the first level, and see if that works)
- If it works, continue on, and add the next level.
- Continue until you find the error.

 

[Norie]

Can you explain the logic behind the expression?

 

[toilettenseife]

Ok, I'll do some testing. Thanks!

 

[toilettenseife]

Can you explain the logic behind the expression?

Sometimes dates are missing from my data. If the date I need is missing, I want use an earlier one. However, all this is happening behind the scenes. This expression was created to notify the user of the date that was used.

 

[james_lankford]

I don't know about the missing operator, but I don't see how the code should do what you want

maybe I'm missing something, but doesn't an IIf statement work this way

iif ( condition is true, do this, otherwise do this ...

so let's say the first condition is false
meaning
[LOGISTICS DELIVERY DATE_CS] is not null --- it actually has data

since the condition is false the iif will jump to the "otherwise" part -- the next iif

but look at the next iif
its checking to see if [LOGISTICS DELIVERY DATE_CS] is null

and we already know its not

so I'm confused how this is supposed to work

If
( 
  [Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null, 
  'Supplier Receipt Date',
  IIf
  (
    [Closed Order File].[LOGISTICS DELIVERY DATE_CS] Is Null And [Closed Order File].[SUPPLIER_RECEIPT_DATE] Is Null, 
    'Logistics P/U Date',