Axess expression nested Iif() statement syntax error

Question

I am working in Access and I need to designate certain records with flags, depending on whether or not the record had been produced or ordered within a date window in a form. I am having trouble with the expression for this (I am using design view to do this), which started like this:

Designation: 
IIf([InvoiceList.PurchaseOrderDt] Between [Forms]![Date Range]![StartDate] 
    And [Forms]![Date Range]![EndDate], 
    "New Backlog", 
    IIf(([InvoiceList.PurchaseOrderDate] < [Forms]![Date Range]![StartDate]) 
        AND (([InvoiceList.FinalBillDate]  Is Null) 
        OR ([InvoiceList.FinalBillDate] > [Forms]![Date Range]![StartDate])),
        “Old Backlog”,
     ””)
   )

I got a syntax error ("The expression you entered has invalid syntax, you may have entered an operand without an operator"), and Backlog" is highlighted in the second statement. Because I'm a little new to access and SQL, I decided to nest the IIF statement further, and get rid of the AND and OR operators, because I wasn't sure how they are treated in Access/SQL. That turned into the following:

Designation: 
IIf([InvoiceList.PurchaseOrderDt] Between [Forms]![Date Range]![StartDate] 
    And [Forms]![Date Range]![EndDate],
    "New Backlog", 
    IIf([InvoiceList.PurchaseOrderDt] < [Forms]![Date Range]![StartDate],
        IIf([InvoiceList.FinalBillDate]  Is Null,
           “Old Backlog”, 
            IIf( [InvoiceList.FinalBillDate] > [Forms]![Date Range]![StartDate],
                “Old Backlog”,
                ””)
           )
       ,””)
    )

But I have the same error. A possible insight from the second code was that the first "Old Backlog"'s Backlog" was still highlighted, not the second. This leads me to think it may be a problem with using 'Is Null', but changing that expressiono to IsNull([InvoiceList.FinalBillDate])did not change the problem.

It is also worth noting that deleting the highlighted portion of the code got rid of the error, but I need this second designation.

My final thought is that it has something to do with the " I'm using. I look forward to hearing your input.

I also tried to format my code so that it would be easy to read.

Johnny Bones Johnny Bones · Accepted Answer · 2013-07-11T19:24:56

Looks to me like the double-quotes are screwy. Access is particular about that. Delete “Old Backlog” and re-type it.

Axess expression nested Iif() statement syntax error

1 Answers