no criteria

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.
 
A

Allen Browne

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)))
 
B

Brian

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 '*')))
 
K

KARL DEWEY

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

nested if in criteria 5
delete query using inner join 2
Query Criteria Not Working 2
Problem with Criteria 12
IIF Expression returning Zero Records in Query 2
iif in the criteria 3
Change criteria based off of Option Group 7
Date comparison in IIF statement in a query 1

Top