Solved Using a Does Not Contain Criteria in an IIf Statement

[StevenSkub]

Hello! I usually never post but I really cannot find a solution to my problem anywhere online.
I am trying to use the does not contain expression "<>" within an IIf statement in my query criteria and I cannot get it to work properly.

I am trying to set things up so that when Combo35 has "HorizonLLC" selected, only records with "HorizonLLC" in the CompName field are returned. When Combo35 has "Contractor" selected, all records that DON'T contain "HorizonLLC" are returned. Finally, if neither are selected, all records are returned. Right now it is working as intended except for the "Contractor" situation.

This is what I have in the Criteria box:
IIf([forms]![frmReports]![Combo35]="HorizonLLC","HorizonLLC",IIf([forms]![frmReports]![Combo35]="Contractor",<>"HorizonLLC",[CompName]))

And when I have the following in there it does give me the non-HorizonLLC values so I don't see why it's not working within the IIf statement:
<> "HorizonLLC"

Any help is greatly appreciated!

 

[theDBguy]

Can you post your query's SQL statement? Thanks.

 

[MajP]

This part will not work.

<>"HorizonLLC"

It will work on the first part because you are returning a simple string but you are tying to return an expression which is not possible.

 

[Pat Hartman]

What do you think the "<>" is doing?

An IIF
IIf(condition, truepathreturn, falsepathreturn) The only part of the expression where you would use a relational operator is in the "condidion" clause. You have the "<>" embedded in the truepathreturn

I'm surprised you are not getting an error message.

 

[MajP]

Make a calculated field
ContractorType: iff([yourField] = "HorizonLLC", "HorizonLLC","Contractor")

then the criteria could be
IIf([forms]![frmReports]![Combo35]="HorizonLLC","HorizonLLC",IIf([forms]![frmReports]![Combo35]="Contractor","Contractor")

 

[cheekybuddha]

You don't need IIf()

Try making the WHERE clause:

...
WHERE ([forms]![frmReports]![Combo35] = 'HorizonLLC' AND CompName LIKE '*HorizonLLC*')
   OR ([forms]![frmReports]![Combo35] = 'Contractor' AND CompName NOT LIKE '*HorizonLLC*')
   OR [forms]![frmReports]![Combo35] IS NULL

 

[Mike Krailo]

I am trying to set things up so that when Combo35 has "HorizonLLC" selected, only records with "HorizonLLC" in the CompName field are returned. When Combo35 has "Contractor" selected, all records that DON'T contain "HorizonLLC" are returned. Finally, if neither are selected, all records are returned. Right now it is working as intended except for the "Contractor" situation.

Since "HorizonLLC" and "Contractor" look to be different types of data (company and company type), I assume your combo has multiple columns. Is that right? Otherwise that combo wouldn't make sense. If that is the case, you would need to take into account the column number as well.

 

[MarkK]

I don't know if this would work, but it did occur to me. Might offer another idea...

1) Make MajP's calculated field as posted in #5, but do it like...

Selected: IIF(YourField = "HorizonLLC", "Yes", "No")

2) Set up combo35 to have three rows and two columns, with the first column bound and hidden.
3) Make combo35's RowSource...

Yes;HorizonLLC;No;Contractor;*;All

...so if you select either of the first two you'll get a match or not a match, but if you select All, you get a wildcard, which should return both.
4) Then, given that LIKE is the same as = when there are no wildcards, make the criteria of the calculated field...

LIKE [forms]![frmReports]![Combo35]

 

[StevenSkub]

Thank you everyone for the feedback! I ended up just making three separate queries, which then feed into three separate reports. One for HorizonLLC, one for Contractor, and one for Both. And then I made it so when a button is pressed it runs the right report based on the selection in the combo box. If I had more time for this project, I would definitely use one of the more elegant solutions you all provided. I'll refer back to this if I need to do something similar in the future. I'm also coming in and editing an existing database so I would do a LOT of things differently if it had been built from the ground up by me. Not the least of which being having Company/Contractor field across the board.

This was very very useful in filtering what I needed in a clean way though, so thank you again!
ContractorType: IIF([CompName] = "HorizonLLC", "HorizonLLC", "Contractor")