How do I simulate "no criteria" as an option in an IF statement?

[Guest]

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Jeff Boyce]

Paul

Have you tried a criterion like:

Like * & [Forms]![TestRecords_Query]![Personnel]

 

[Guest]

Jeff

When I type that, Access changes it to
IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),([TestRecords].[Personnel])
Like "*" & [TestRecords]![Personnel],[Forms]![TestRecords_Query]![Personnel])
and NO results are returned!

Paul

Have you tried a criterion like:

Like * & [Forms]![TestRecords_Query]![Personnel]

--
Good luck

Jeff Boyce
<Access MVP>

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[PC Datasheet]

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

 

[Jeff Boyce]

Paul

I wasn't proposing that you embed that in your IIF() statement. I was
suggesting using that as the criterion. Perhaps I don't fully understand
your design.

Could you post the SQL statement of your query?

--
Good luck

Jeff Boyce
<Access MVP>

Jeff

When I type that, Access changes it to
IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),([TestRecords].[Personne
l])
Like "*" & [TestRecords]![Personnel],[Forms]![TestRecords_Query]![Personnel])
and NO results are returned!

Paul

Have you tried a criterion like:

Like * & [Forms]![TestRecords_Query]![Personnel]

--
Good luck

Jeff Boyce
<Access MVP>

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of

my
IF

statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

Sorry guys but no luck so far.

I tried the following in many formats but Access keep complaining that it is
too complex:
IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),([TestRecords]![Personnel]
Or ([TestRecords]![Personnel] Is
Null)),[Forms]![TestRecords_Query]![Personnel])

My basic need is that if the user runs the query from the form while leaving
all the fields blank, the query should return all the records from the table.
The user should then be able to select a value for any field (dropdownbox)
and any number of fields and by so doing, limit the number of results.

You see, a query with all the columns from a table, with not criteria set
for each column, will return all the records from the table, and then as you
add criteria to columns, the results will reduce. My users won't be able to
do this in the design view, so I have to provide them with a form from where
the query will be built automatically.

Regards
Paul

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

Jeff
Here is the SQL code:

SELECT TestRecords.TestDate, TestRecords.TestType, TestRecords.LandType,
TestRecords.Personnel
FROM TestRecords
WHERE
(((TestRecords.TestDate)=IIf(IsNull([Forms]![TestRecords_Query]![TestDate]),[TestRecords]![TestDate],[Forms]![TestRecords_Query]![TestDate]))
AND
((TestRecords.TestType)=IIf(IsNull([Forms]![TestRecords_Query]![TestType]),[TestRecords]![TestType],[Forms]![TestRecords_Query]![TestType]))
AND
((TestRecords.LandType)=IIf(IsNull([Forms]![TestRecords_Query]![LandType]),[TestRecords]![LandType],[Forms]![TestRecords_Query]![LandType]))
AND
((TestRecords.Personnel)=IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![TestDate],[Forms]![TestRecords_Query]![Personnel])));

The true part of the IF statement should return ALL the fields and not only
the non-null fields. I tried the suggestion to use:

[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

there but Access complains about the complexity!!!

My basic need is that the user can select the criteria for the query and
omit it as well. It the user leave the DATE blank, tests from all dates
should be returned. It a date is selected, all tests from that day only
should be returned. And this should be possible for all 30 fields on my form!

I hope you have another idea!

Regards

Paul

I wasn't proposing that you embed that in your IIF() statement. I was
suggesting using that as the criterion. Perhaps I don't fully understand
your design.

Could you post the SQL statement of your query?

--
Good luck

Jeff Boyce
<Access MVP>

Jeff

When I type that, Access changes it to
IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),([TestRecords].[Personne
l])
Like "*" & [TestRecords]![Personnel],[Forms]![TestRecords_Query]![Personnel])
and NO results are returned!

Paul

Have you tried a criterion like:

Like * & [Forms]![TestRecords_Query]![Personnel]

--
Good luck

Jeff Boyce
<Access MVP>

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my
IF
statement, I.e. if the condition is true, the critria for the query is
such
that there is no criteria (as if I left the critria row blank in query
design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a
query
that only list unique values) and the query list all the records where
this
value is found - BUT if the user DOESN'T select any value -> the query
list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:


IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

I miss understood your suggestion and it works perfect for one field, I tried
it then for muliple fields and it simply ignored the other field values???
I saw that Access populates the "or" parts atuomatically into a big matrix
of options and it makes perfect sence, it just doesn't seem to work right.

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

Sorry about all the imature replies - I deleted the whole think and started
from scrath with you suggestion posted in every column and it works like a
charm!
Thank you so much!!

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

I think I made a small type. I started from scratch and it worked fine!
Now I seem to have one more problem: I have too many fields in my query and
Access now basically came to a standstill after I pasted the code into every
column of the query!!

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.

 

[Guest]

Is there any shortcut for an example like this:

I have 4 elements that utilize this "no criteria" clause. If I want to add
another combo box on the form, is there a shortcut to add that box to the "no
criteria" collection without having to delete all of them and start from
scratch each time?

Sorry about all the imature replies - I deleted the whole think and started
from scrath with you suggestion posted in every column and it works like a
charm!
Thank you so much!!

Use the following for your criteria:
[Forms]![TestRecords_Query]![Personnel] Or
[Forms]![TestRecords_Query]![Personnel] Is Null

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I want to do:

I want to simulate the "no criteria" scenario in my true expression of my IF
statement, I.e. if the condition is true, the critria for the query is such
that there is no criteria (as if I left the critria row blank in query design
view).

Let me explain:
The user select a value from a combobox (which gets its values from a query
that only list unique values) and the query list all the records where this
value is found - BUT if the user DOESN'T select any value -> the query list
all records from the original table (including records with NULL values) -
this should be like having a query with no criteria set.

I frist tried leaving the true part of the IF statement empty - that's not
allowed and "*" or [] etc. also doesn't give the desired result.

I then tried:

IIf(IsNull([Forms]![TestRecords_Query]![Personnel]),[TestRecords]![Personnel
],[Forms]![TestRecords_Query]![Personnel])

Here the true part list all the records from the original table but
unfortunately exludes the records where [Personnel] is left empty (null).

Hope someone can help.