Using an If in the criteria of a query

[darkhat01]

I am trying to use a string filter in the criteria in the query, but I can't seem to get the if statement to work correctly, any ideas would be great on what I could try next?

The [PortfolioName] in the report [Rtest] is an invisible text box on the report, the value is selected by a choice on a form. If I put “Like [Reports]![Rtest]![PortfolioName]” in the criteria in the query it works to select the company. But I want to select Enterprise and include all of the companies. (CompA, CompB, CompC)

Here is what I wrote and it is not working.

If (Like [Reports]![Rtest]![PortfolioName] = "Enterprise") then In("CompA","CompB","CompC") Else Like ([Reports]![RSTtest]![PortfolioName]) End If

Thanks,

Darkhat

 

[traingamer]

You need to use the 'immediate if' function iif().

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[darkhat01]

So I changed it to:

iif(Like [Reports]![Rtest]![PortfolioName] = "Enterprise" then In("CompA","CompB","CompC") Else(Like [Reports]![Rtest]![PortfolioName]) endif

But I am getting a "The expression you entered contains invalid syntax. You may have entered an operand without an operator." I am not seeing the problem. Any idea?

Darkhat

 

[PHV]

What about this criteria ?
Like [Reports]![RSTtest]![PortfolioName] OR (In ('CompA','CompB','CompC') AND [Reports]![RSTtest]![PortfolioName]='Enterprise')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

the syntax for the IIF statement is:

IIF([COLOR=green]LogicalTest[/color], [COLOR=blue]Value if True[/color], [COLOR=red]Value if False[/color])

so you would need:

IIF([COLOR=green]Like [Reports]![Rtest]![PortfolioName] = "Enterprise"[/color], [COLOR=blue]In("CompA","CompB","CompC")[/color], [COLOR=red]Like [Reports]![Rtest]![PortfolioName][/color])

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[darkhat01]

Leslie,

I am not getting errors now so this is good and I know I am close, but I am not getting any data returned in the query. When I run the report Rtest I know that there is a value in PortfolioName, I made it visible so I could see what was being places in it. If I select Enterprise it is Enterprise, if I select CompA it is CompA and so on...

Enterprise, CompA, CompB, CompC are all text, this is what I am searching on. What I want to do is if I select Enterprise it includes all three companies.

When I entered in what you suggested Access automatically added the [SHARED_EIPPROJECT].[Porfolio] Table.. I believe this is fine.... But here is what the If statement looks like now.

IIf(([SHARED_EIPPROJECT].[Porfolio]) Like [Reports]![Rtest]![PortfolioName]="Enterprise",([SHARED_EIPPROJECT].[Porfolio]) In ("CompA","CompB","CompC"), ([SHARED_EIPPROJECT].[Porfolio]) Like [Reports]![Rtest]![PortfolioName])

Thanks,

Darkhat

 

[PHV]

Anyway, the IIf way is a dead end.

 

[lespaul]

I'm with PHV, even though you now know the syntax for the iif, it's not what you want to do...did you try his previous suggestion?

 

[darkhat01]

PHV your first suggestion did work... But I don't understand why or how the logic works, and why the iif statement does not work. Also why are you using ' (Single quote) instead of " (Double Quotes) I thought that with text you had to always use Double quotes to make it work...

The OR and the AND is what is throwing me off. If you have the time can you walk me through this?


Like [Reports]![RSTtest]![PortfolioName] OR (In ('CompA','CompB','CompC') AND [Reports]![RSTtest]![PortfolioName]='Enterprise')


Thanks so much for your help.

Darkhat

 

[PHV]

In the SQL view have a look at the generated WHERE clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

And, as for the single quotes, it's just an habits so I can use the SQL code in VBA.

 

[darkhat01]

Spent more time reading about how it worked.... I understand it now.....

Thanks PHV...

Darkhat