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.

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.

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.

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.