nested if in criteria

  • Thread starter Thread starter seeker
  • Start date Start date
S

seeker

i have the following in criteria of a query.

IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'wholesale',
"N", forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'retail',"Y",
"null")

Error says that i do not have the correct arguments. What am i missing.
Thanks.
 
IIF(forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'wholesale',
"N",
IIF(forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'retail',"Y",
"null"))

Regards

Kevin
 
This is the correct syntax;

IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL)))

but now I have the problem of it the txtbox = 'all' the i do not want any
criteria and placing NULL in the iif clause does not work.
 
You do not need the third IIF. You have three potential assingments: N, Y,
Null.

In your formulation below you do not have a value for when the test = 'All'
is False

Regards

kevin

seeker said:
This is the correct syntax;

IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL)))

but now I have the problem of it the txtbox = 'all' the i do not want any
criteria and placing NULL in the iif clause does not work.

seeker said:
i have the following in criteria of a query.

IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchivetype =
'wholesale',
"N", forms!frmarchiveinvoicesoptions!txtacceptedarchivetype =
'retail',"Y",
"null")

Error says that i do not have the correct arguments. What am i missing.
Thanks.
Click to expand...
Click to expand...
 
I see you are trying to use this as criteria.

So is does the field contain strings of "Y" or "N" or is the field you are
filtering on a Yes/No (Boolean) field?

Try entering the following as your criteria if the field is a boolean field
(all in one criteria "cell" under the field.

IIF([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]="wholesale",False,True)
OR [forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype] = "All"

IF the field actually contains "Y", "N", or other values (null, zero-length
string, etc.) then use this.

IIF([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]="wholesale","N","Y")
OR [forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype] = "All"

Access will rearrange this when you save the query, but the query should still
work in most cases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Try this --
Like
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',"*")))

If your field may have nulls then add -- OR Null to the above.

--
Build a little, test a little.


seeker said:
This is the correct syntax;

IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL)))

but now I have the problem of it the txtbox = 'all' the i do not want any
criteria and placing NULL in the iif clause does not work.

seeker said:
i have the following in criteria of a query.

IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'wholesale',
"N", forms!frmarchiveinvoicesoptions!txtacceptedarchivetype = 'retail',"Y",
"null")

Error says that i do not have the correct arguments. What am i missing.
Thanks.
Click to expand...
Click to expand...
 
Back
Top