Operand without an operator error

[mdnikki]

I am trying to put this in a query.


I don't see what I am missing here when I try to save this I am getting this error invalid syntax and the operand without an operator. Can someone see what I am missing.

Delinquency Category: IIf([Days Overdue]<90,"Current",IIf([Days Overdue] Between 90 And 179,"3 to 6 Months",IIf([Days Overdue] Between 180 And 364,"6 to 12 Months",IIf([Days Overdue] Between 365 And 730,"1 to 2 Years",IIf([Days Overdue] Between 731 And 1094,"2 to 3 Years”, IIf([Days Overdue] Between 1095 And 1459,"2 to 3 Years”, IIf([Days Overdue] Between 1460 And 1825,"4 to 5 Years", "5 Years or More"))))))))



I should say the original query had this and worked fine, I just added a few more categories and thats when it gave me the error

Delinquency Category: IIf([Days Overdue]<90,"Current",IIf([Days Overdue] Between 90 And 179,"3 to 6 Months",IIf([Days Overdue] Between 180 And 364,"6 to 12 Months",IIf([Days Overdue] Between 365 And 730,"1 to 2 Years",IIf([Days Overdue] Between 731 And 1495,"2 to 3 Years","3 Years or More")))))

[ranman256]

1. you really should not use multi nested IIFs. Its a pain to program, read and debug.
instead, use a table with MIN, MAX values to pull the result
or
use a custom Function that does similar to the IIF but more readable and easier to program.

function getOverDueMsg(pvDays)
select case true
    case pvDays<90
       getOverDueMsg= "Current"
    case pvDays>90 and pvDays <179
       getOverDueMsg= "3 to 6 Months"
end function

2. I dont think you can use BETWEEN in the IIf. This only works in query criteria, not here.

[ssanfu]

,IIf([Days Overdue] Between 731 And 1094,"2 to 3 Years”, IIf([Days Overdue] Between 1095 And 1459,"2 to 3 Years”, IIf([Days Overdue] Between 1460 And 1825,"4 to 5 Years",

The problem is that the 2 closing double quotes (in red above) is not a standard double quote. If you delete the double quote, then retype the double quote, it should work.... at least it did for me.

Also the text is wrong for the "Between 1095 And 1459" comparison.
Look at this

Delinquency Category: 
IIf([Days Overdue]<90,"Current",
IIf([Days Overdue] Between 90 And 179,"3 to 6 Months",
IIf([Days Overdue] Between 180 And 364,"6 to 12 Months",
IIf([Days Overdue] Between 365 And 730,"1 to 2 Years",
IIf([Days Overdue] Between 731 And 1094,"2 to 3 Years”,   << -- fix the double quotes (in red)
IIf([Days Overdue] Between 1095 And 1459,"2 to 3 Years”,   << --should be "3 to 4 Years"  /fix the double quotes (in red)
IIf([Days Overdue] Between 1460 And 1825,"4 to 5 Years", "5 Years or More")))))))      <<- should be 7 closing parns

NOTE: Should never use spaces in object names....


If you decide to go the way ranman suggested, the function would be

Function getOverDueMsg(pvDays As Long) As String
    Select Case True
        Case pvDays < 90
            getOverDueMsg = "Current"
        Case pvDays >= 90 And pvDays < 179
            getOverDueMsg = "3 to 6 Months"
        Case pvDays >= 180 And pvDays < 364
            getOverDueMsg = "6 to 12 Months"
        Case pvDays >= 365 And pvDays < 730
            getOverDueMsg = "1 to 2 Years"
        Case pvDays >= 731 And pvDays < 1094
            getOverDueMsg = "2 to 3 Years"
        Case pvDays >= 1095 And pvDays < 1459
            getOverDueMsg = "3 to 4 Years"
        Case pvDays >= 1460 And pvDays < 1825
            getOverDueMsg = "4 to 5 Years"
        Case pvDays >= 1826
            getOverDueMsg = "5 Years or More"
    End Select

End Function

To use it in a query:

DelinquencyCategory: getOverDueMsg([Days Overdue])

[mdnikki]

Thank you removing the quotes and replacing them worked. I appreciate your help.

[ssanfu]

Glad to help.

Ready to mark this solved??