Query Criteria with IFF statement and Checkbox

[acarterczyz]

Hi Everyone,

I'm having an issue getting my query to ommit records with a blank field - in fact, it ommits all records.

What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.

Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)

 

[CJ_London]

If you just want to exclude records without email, in your query just put

Not is null

in the criteria for email

 

[boblarson]

Or

Is Not Null

 

[HiTechCoach]

Hi Everyone,

I'm having an issue getting my query to ommit records with a blank field - in fact, it ommits all records.

What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.

Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)

I assume the IIF is used to select if you want only records with an email address. Is that correct?

It would really help to see all of your SQL so we can see how you are using the IIF.

 

[acarterczyz]

SELECT Detail.*, *
FROM Detail
WHERE (((Detail.Primary)=IIf([Forms]![AdvancedReporting]![Combo216]="ALL","*",[Forms]![AdvancedReporting]![Combo216])) AND ((Detail.[Asgn Type])=IIf([Forms]![AdvancedReporting]![Check226]=False,IIf([Forms]![AdvancedReporting]![Check224]=False,IIf([Forms]![AdvancedReporting]![Check222]=False,"*",'NPP'),'SG'),'YB')) AND ((Detail.[E-Mail])=IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",(Detail.[E-Mail]) Is Not Null)));

- Detail is the table I'm pulling from.
- AdvancedReporting is the Form I have the checkbox on that needs to filter.
- Primary and Asgn Type and E-Mail are columns on the table.

When I use just the Primary and Asgn Type parts of the code, it works perfectly. But the email portion of the code is causing it to return nothing.

 

[HiTechCoach]

SELECT Detail.*, *
FROM Detail
WHERE (((Detail.Primary)=IIf([Forms]![AdvancedReporting]![Combo216]="ALL","*",[Forms]![AdvancedReporting]![Combo216])) AND ((Detail.[Asgn Type])=IIf([Forms]![AdvancedReporting]![Check226]=False,IIf([Forms]![AdvancedReporting]![Check224]=False,IIf([Forms]![AdvancedReporting]![Check222]=False,"*",'NPP'),'SG'),'YB')) AND ((Detail.[E-Mail])=IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",(Detail.[E-Mail]) Is Not Null)));

- Detail is the table I'm pulling from.
- AdvancedReporting is the Form I have the checkbox on that needs to filter.
- Primary and Asgn Type and E-Mail are columns on the table.

When I use just the Primary and Asgn Type parts of the code, it works perfectly. But the email portion of the code is causing it to return nothing.

I don't see how any of your Where clause will work correctly using "=". You will need to use "Like" if you want to use the "*" wildcard to select all.

FWIW: If this were my project I would use VBA code to create the SQL based on the form's selections.

If you must do this in a query then I would recommend a few things you can do to make this work easier and the SQL execute faster.

1) [Forms]![AdvancedReporting]![Combo216] (TIP: use meaningful control names)

I would update the [Combo216] combo box to have two columns. The bound column would return "*" for the "ALL" selection.

This would simplify the Where clause to be just:

(Detail.Primary) Like [Forms]![AdvancedReporting]![Combo216]

2) [Forms]![AdvancedReporting]![Check230]

I assume that if selected (checked) you only want records with email addresses. If not checked you want all records If this is correct then I would use:

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230]

 

[acarterczyz]

Thanks! But when I use the following, I get an 'invalid arguement' error:

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230])

 

[CJ_London]

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230])

The problem with only posting some of the code is difficult to check in context.

So, you have a superflous comma before detail (highlighted in red) and what appears to be one at the end

The iif statement is not actually required as such so this would achieve the same thing

Detail.[E-Mail] is not null

 

[HiTechCoach]

Sorry about that. That was untested code. I have no way of testing the code at the moment.

As soon as I get back to my computer I will test the code. Note: Since I don't have a copy of your database it hard to test the code.

 

[acarterczyz]

The problem with only posting some of the code is difficult to check in context.

So, you have a superflous comma before detail (highlighted in red) and what appears to be one at the end

The iif statement is not actually required as such so this would achieve the same thing

We're getting closer!

Now regardless if the checkbox is checked or not, it shows all records.

 

[HiTechCoach]

Thanks! But when I use the following, I get an 'invalid arguement' error:

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230])

Looks like I left out an ending )


Try:

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null),True,False)= [Forms]![AdvancedReporting]![Check230])

 

[acarterczyz]

Looks like I left out an ending )


Try:

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null),True,False)= [Forms]![AdvancedReporting]![Check230])

When I use that code, I get "The expression you entered has too many closing parentheses."

And I put a parenteses after the = and regardless of if the checkbox is checked or not, it returns all fields (blank and non-blank).

 

[HiTechCoach]

The iif statement is not actually required as such so this would achieve the same thing

Detail.[E-Mail] is not null

It is my understading that If the checkbox is NOT selected then they do not want to filter on email address. Using Detail.[E-Mail] is not null assumes that they Checkbox is selected. How does your solution include records with no email address(Detail.[E-Mail] is null) if the checkbox is not selected?

 

[acarterczyz]

It is my understading that If the checkbox is NOT selected then they do not want to filter on email address. Using Detail.[E-Mail] is not null assumes that they Checkbox is selected. How does your solution include records with no email address(Detail.[E-Mail] is null) if the checkbox is not selected?

When the checkbox is selected, I want only the records that have the email filled out. If the checkbox is not selected, I want all records.

 

[HiTechCoach]

I just made it back to my PC. Let me do some testing.

 

[acarterczyz]

I just made it back to my PC. Let me do some testing.

Thank you

 

[HiTechCoach]

This should work:

IIF( [Forms]![AdvancedReporting]![Check230] and  not IsNull(Detail.[E-Mail]),True,False) = [Forms]![AdvancedReporting]![Check230]

I will post the example I created to test this as soon as I get it cleaned up.

 

[CJ_London]

The problem with this statement

IIF([Forms]![AdvancedReporting]![Check230]=-1 and (Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230])

Take out the unrequired brackets we get

IIF([Forms]![AdvancedReporting]![Check230]=-1 and Detail.[E-Mail] is not null,True,False)= [Forms]![AdvancedReporting]![Check230]

check the logic:
when checkbox = true and email is not null
iif(true and true, true, false)=true => solve: true=true => Ans: True
when checkbox =true and email is null
iif(true and false,true,false)=true => solve: false=true =>Ans: False
when checkbox=false and email is not null
iif(false and true,true,false)=false => solve: false=false => Ans: True
when checkbox =false and email is null
iif(false and false,true,false)=false => solve: false=false => Ans: True

This seems OK to me

 

[HiTechCoach]

Here is a working example in Access 2000 format. It should work with Access 2000 and later. I tested it in 2010.

 

[acarterczyz]

This should work:

IIF( [Forms]![AdvancedReporting]![Check230] and  not IsNull(Detail.[E-Mail]),True,False) = [Forms]![AdvancedReporting]![Check23T]

I will post the example I created to test this as soon as I get it cleaned up.

When I used that formula, it won't return anything when the checkbox is checked, but when it is unchecked, it returns all records. My field is set to text formatting, could this be why?