sql - Axess expression nested Iif() statement syntax error -


i working in access , need designate records flags, depending on whether or not record had been produced or ordered within date window in form. having trouble expression (i using design view this), started this:

designation:  iif([invoicelist.purchaseorderdt] between [forms]![date range]![startdate]      , [forms]![date range]![enddate],      "new backlog",      iif(([invoicelist.purchaseorderdate] < [forms]![date range]![startdate])          , (([invoicelist.finalbilldate]  null)          or ([invoicelist.finalbilldate] > [forms]![date range]![startdate])),         “old backlog”,      ””)    ) 

i got syntax error ("the expression entered has invalid syntax, may have entered operand without operator"), , backlog" highlighted in second statement. because i'm little new access , sql, decided nest iif statement further, , rid of , and or operators, because wasn't sure how treated in access/sql. turned following:

designation:  iif([invoicelist.purchaseorderdt] between [forms]![date range]![startdate]      , [forms]![date range]![enddate],     "new backlog",      iif([invoicelist.purchaseorderdt] < [forms]![date range]![startdate],         iif([invoicelist.finalbilldate]  null,            “old backlog”,              iif( [invoicelist.finalbilldate] > [forms]![date range]![startdate],                 “old backlog”,                 ””)            )        ,””)     ) 

but have same error. possible insight second code first "old backlog"'s backlog" still highlighted, not second. leads me think may problem using 'is null', changing expressiono isnull([invoicelist.finalbilldate])did not change problem.

it worth noting deleting highlighted portion of code got rid of error, need second designation.

my final thought has " i'm using. forward hearing input.

i tried format code easy read.

looks me double-quotes screwy. access particular that. delete “old backlog” , re-type it.


Comments