|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 9th, 2009, 01:44 AM
posted to microsoft.public.access.queries
|
|||
|
|||
nested if in criteria
i have the following in criteria of a query.
IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchiv etype = 'wholesale', "N", forms!frmarchiveinvoicesoptions!txtacceptedarchive type = 'retail',"Y", "null") Error says that i do not have the correct arguments. What am i missing. Thanks. 
|
|
#2
December 9th, 2009, 02:03 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
nested if in criteria
IIF(forms!frmarchiveinvoicesoptions!txtacceptedarc hivetype = 'wholesale', "N", IIF(forms!frmarchiveinvoicesoptions!txtacceptedarc hivetype = 'retail',"Y", "null")) Regards Kevin "seeker" wrote in message news  i have the following in criteria of a query. IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchiv etype = 'wholesale', "N", forms!frmarchiveinvoicesoptions!txtacceptedarchive type = 'retail',"Y", "null") Error says that i do not have the correct arguments. What am i missing. Thanks.
|
|
#3
December 9th, 2009, 02:51 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
nested if in criteria
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" wrote: i have the following in criteria of a query. IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchiv etype = 'wholesale', "N", forms!frmarchiveinvoicesoptions!txtacceptedarchive type = 'retail',"Y", "null") Error says that i do not have the correct arguments. What am i missing. Thanks.
|
|
#4
December 9th, 2009, 02:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
nested if in criteria
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 seeker wrote: i have the following in criteria of a query. IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchiv etype = 'wholesale', "N", forms!frmarchiveinvoicesoptions!txtacceptedarchive type = 'retail',"Y", "null") Error says that i do not have the correct arguments. What am i missing. Thanks.
|
|
#5
December 9th, 2009, 03:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
nested if in criteria
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" wrote: 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" wrote: i have the following in criteria of a query. IIf (forms!frmarchiveinvoicesoptions!txtacceptedarchiv etype = 'wholesale', "N", forms!frmarchiveinvoicesoptions!txtacceptedarchive type = 'retail',"Y", "null") Error says that i do not have the correct arguments. What am i missing. Thanks.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
