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
|
|||
|
|||
no criteria
the following iif
IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL))) attempts to place no criteria filter in the query when 'all' is found. the query runs and does not transfer all records to another database. how would this iif statement look so that 'all' wold not have any criteria in the query. |
#2
|
|||
|
|||
no criteria
The WHERE clause of the query evaluates to an expression that is True or
False (or Null.) You need to craft this expression so that it returns TRUE when the text box contains All. Switch the query to SQL View (View menu.) Locate the WHERE clause, and change it to this kind of thing (replacing "somefield" with the name of your text field): WHERE IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale', [somefield]="N", IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail', [somefield]="Y", IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',TRUE))) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "seeker" wrote in message ... the following iif IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL))) attempts to place no criteria filter in the query when 'all' is found. the query runs and does not transfer all records to another database. how would this iif statement look so that 'all' wold not have any criteria in the query. |
#3
|
|||
|
|||
no criteria
It is not quite clear from your post where this statement occurs, but
assuming it is actually in the criteria of the query (i.e. not on a form that is creating a criteria string), your IIf filters to actual NULL values when it should be looking for ALL values, which is perhaps best represented by this: Like '*' (i.e. Like anything) I did not test this, but it should look something like this: IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',Like '*'))) "seeker" wrote: the following iif IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL))) attempts to place no criteria filter in the query when 'all' is found. the query runs and does not transfer all records to another database. how would this iif statement look so that 'all' wold not have any criteria in the query. |
#4
|
|||
|
|||
no criteria
You can not put the operator 'Like' inside.
Like IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',"*"))) -- Build a little, test a little. "Brian" wrote: It is not quite clear from your post where this statement occurs, but assuming it is actually in the criteria of the query (i.e. not on a form that is creating a criteria string), your IIf filters to actual NULL values when it should be looking for ALL values, which is perhaps best represented by this: Like '*' (i.e. Like anything) I did not test this, but it should look something like this: IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',Like '*'))) "seeker" wrote: the following iif IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='wholesale',"N",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='retail',"Y",IIf([forms]![frmarchiveinvoicesoptions]![txtacceptedarchivetype]='all',NULL))) attempts to place no criteria filter in the query when 'all' is found. the query runs and does not transfer all records to another database. how would this iif statement look so that 'all' wold not have any criteria in the query. |
Thread Tools | |
Display Modes | |
|
|