no criteria

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

seeker

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.
 
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)))
 
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 '*')))
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top