Use wildcard in conditional query

[Guest]

I have a form with a series of check boxes; the values of the controls are
used as conditional criteria in a query. So if checkbox 2 is checked its
value is true.

In the criteria row of the query grid for field [Category] I have entered
IIF(Forms!MyForm!Checkbox2=True,"X") then all records with Category of X are
returned. This works fine.

If the user checks Checkbox 1 all records should be returned so I have tried:

Iif(Forms!MyForm!Checkbox1=True,"*") also tried Like
Iif(Forms!MyForm!Checkbox1=True,"*")
but neither work. From investigation it looks as if the value of the
checkbox is used i.e. -1 (have tried it out on a field where -1 is a valid
entry.

I also need to use similar criteria to select records where their values are
Null or Is Not Null but I can't get this to work either - just entering Null
or Is Not Null in the criteria is fine its when they are included in the Iif
statement that they fail.

Grateful for any help.

 

[Douglas J. Steele]

The IIf statement has 3 parts. The first needs to evaluate to a Boolean, the
second is what to return if the Boolean is True, and the third is what to
return if the Boolean is false. You're missing the 3rd part.

Once you get that working, then I believe you will need to use Like in your
second question.

Finally, inside of an IIf statement, you don't use Is Null or Is Not Null:
you need to use the IsNull() function instead.

 

[Guest]

Doug

Thanks. Have got a reply from another fourm - see below - that seems to
work OK

Go to the SQL view of your query. You should be able to modify a statement
like below to get your desired results:

SELECT Table1.Name, Table1.Name2
FROM Table1
WHERE IIf([Forms]![MyForm]![Checkbox1]=True,(Table1.Name) Like "*",
(Table1.Name) Is Not Null) ;

Regards

The IIf statement has 3 parts. The first needs to evaluate to a Boolean, the
second is what to return if the Boolean is True, and the third is what to
return if the Boolean is false. You're missing the 3rd part.

Once you get that working, then I believe you will need to use Like in your
second question.

Finally, inside of an IIf statement, you don't use Is Null or Is Not Null:
you need to use the IsNull() function instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form with a series of check boxes; the values of the controls are
used as conditional criteria in a query. So if checkbox 2 is checked its
value is true.

In the criteria row of the query grid for field [Category] I have entered
IIF(Forms!MyForm!Checkbox2=True,"X") then all records with Category of X are
returned. This works fine.

If the user checks Checkbox 1 all records should be returned so I have tried:

Iif(Forms!MyForm!Checkbox1=True,"*") also tried Like
Iif(Forms!MyForm!Checkbox1=True,"*")
but neither work. From investigation it looks as if the value of the
checkbox is used i.e. -1 (have tried it out on a field where -1 is a valid
entry.

I also need to use similar criteria to select records where their values are
Null or Is Not Null but I can't get this to work either - just entering Null
or Is Not Null in the criteria is fine its when they are included in the Iif
statement that they fail.

Grateful for any help.

 

[John Spencer (MVP)]

Douglas,

Pardon me, if I disagree with your statement

"Finally, inside of an IIf statement, you don't use Is Null or Is Not Null: you
need to use the IsNull() function instead."

In SQL you can use the SQL operators in an IIF clause. There is an SQL IIF and
there is a VBA IIF and they work a bit differently.

For instance
SELECT FAQ.fPriority,
IIf([fPriority] Is Not Null,"X","Y") AS Expr1
FROM FAQ;

Returns X or Y depending on whether or not the field fPriority is null.

The IIf statement has 3 parts. The first needs to evaluate to a Boolean, the
second is what to return if the Boolean is True, and the third is what to
return if the Boolean is false. You're missing the 3rd part.

Once you get that working, then I believe you will need to use Like in your
second question.

Finally, inside of an IIf statement, you don't use Is Null or Is Not Null:
you need to use the IsNull() function instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form with a series of check boxes; the values of the controls are
used as conditional criteria in a query. So if checkbox 2 is checked its
value is true.

In the criteria row of the query grid for field [Category] I have entered
IIF(Forms!MyForm!Checkbox2=True,"X") then all records with Category of X are
returned. This works fine.

If the user checks Checkbox 1 all records should be returned so I have tried:

Iif(Forms!MyForm!Checkbox1=True,"*") also tried Like
Iif(Forms!MyForm!Checkbox1=True,"*")
but neither work. From investigation it looks as if the value of the
checkbox is used i.e. -1 (have tried it out on a field where -1 is a valid
entry.

I also need to use similar criteria to select records where their values are
Null or Is Not Null but I can't get this to work either - just entering Null
or Is Not Null in the criteria is fine its when they are included in the Iif
statement that they fail.

Grateful for any help.