function has the wrong number of arguments

[Darla]

Hello, can anyone help me figure out what is wrong with my below code? I keep getting the "wrong number of arguments" error in this query

Status: IIf([QStatus]= "Open" And [Type] ="Check In") Or ([QStatus] "Open" and [Type] ="Check Out"), Or [QStatus] ="Closed" and [Type] Is Null And [checkoutDateIn] is Null), "Checked Out"), IIf([QStatus] = "Closed" And [Type] Is Null And [checkoutDateIn] Is Not Null, "Checked In"))

[ranman256]

extra paren needed at start

IIf( ([QStatus]= "Open" And [Type] ="Check In") Or

[ranman256]

extra paren needed at start, and extra comma

IIf( ([QStatus]= "Open" And [Type] ="Check In") Or

Status:
IIf( ([QStatus]= "Open" And [Type] ="Check In") Or
([QStatus] "Open" and [Type] ="Check Out") Or
([QStatus] ="Closed" and [Type] Is Null And [checkoutDateIn] is Null), "Checked Out",

IIf( ([QStatus] = "Closed" And [Type] Is Null And ([checkoutDateIn] is not null)), "Checked In")
)

[orange]

??? Seems this part does not have a false condition

IIf([QStatus] = "Closed" And [Type] Is Null And [checkoutDateIn] Is Not Null, "Checked In")

[Missinglinq]

Two things, before trying to straighten out the hairball that frequently arises out of the use of nested IIF constructs:

First, TYPE is a Reserved Word and shouldn't be used as the name of a Field, as doing so can cause all kinds of problems, perhaps (but not guaranteed) even a part of the problem here.

Secondly, you're using VBA, here, and in VBA the correct syntax for

[Type] Is Null

would be

IsNull([Type])

Likewise

[checkoutDateIn] Is Not Null

would be

Not IsNull([checkoutDateIn])

and so forth.

I'd start off by correcting these missteps and then see what you're left with.

Linq ;0)>

[Darla]

Two things, before trying to straighten out the hairball that frequently arises out of the use of nested IIF constructs:

First, TYPE is a Reserved Word ...

So I've changed it to this and I still get that same error... Do you think it is because I'm using Type as a field name? Or am I missing something else too? I appreciate the help!

IIf(([QStatus]= "Open" And [Type]="Check In") Or [QStatus]="Open" and [Type]="Check Out"), Or [QStatus]="Closed" And IsNull([Type]) And IsNull([checkoutDateIn]),"Checked Out"),IIf([QStatus]="Closed" And IsNull([Type]) And Not IsNull([checkoutDateIn]),"Checked In","Checked Out"))

[orange]

Since you are having issues with a fairly complex iif, why not do some testing/analysis?

General structure/syntax of IIF IIF (condition, result if true, result if false)
Create a new Select query and start with a simple condition

eg

  IIf([QStatus]= "Open" ,"Open", "Not Open")

then add new conditions 1 at a time and check your output

then add another condition and check your output.

Then review you complex iif, compare to your built up version that worked....

Good luck