IIF Statement in Query

[mkov]

What I have currently is 2 different queries that pull the same information but with different criteria. I am trying to combine them into 1 query with an IIF statement for the criteria.

The querie is run based on the RepNo that is input on a form. Once the user choose the RepNo from the combo box on the form, they click a botton and it runs the query. If RepNo = 061 then I want the query only to return AcctNo ABC123456, if it is any RepNo other than 061, I don't want it to return CBA123456. Below is what I have come up with, but it will not return any records. If I remove the IIF and only have the criteria <>CBA123456 for the AcctNo, the query works great.

Can someone tell me what I am doing wrong. I am still somewhat of a beginner at IIF statements.

Thanks.


SELECT tblRepCommissions.Acctno, tblRepCommissions.RepNo
FROM tblRepCommissions
WHERE (((tblRepCommissions.Acctno)=IIf(([Forms]![frmBrokerageCommissions]![RepNo]=&quot;061&quot,(&quot;ABC123456&quot,((tblRepCommissions.Acctno)<>&quot;CBA123456&quot)) AND ((tblRepCommissions.RepNo)=[Forms]![frmBrokerageCommissions]![RepNo]));

 

[PaulBricker]

I would assume you could put this in the criteria for the Acctno field
IIf(RepNo = &quot;061&quot;,&quot;ABC123456&quot;,<>&quot;CBA123456&quot

and
=Forms!frmBrokerageCommissions!RepNo
on the criteria line for RepNo.


Paul

 

[mkov]

Thanks Paul, but that doesn't work either. I just don't get it. I can't see why it won't work.

Thanks.

 

[hneal98]

Try combining your two queries into one query by basing a third query on those two. Treat each query like a table, in other words you need to have a common field for each of the queries to link on.

 

[PaulBricker]

Interesting is works for equal to values but not for does not equal, <>, values
So this would work with the Form criteria in RepNo
IIf(RepNo = &quot;061&quot;,&quot;ABC123456&quot;,&quot;CBA123456&quot
but this doesn't
IIf(RepNo = &quot;061&quot;,&quot;ABC123456&quot;,<>&quot;CBA123456&quot

Paul

 

[hneal98]

try using NOT instead of <>.

 

[PaulBricker]

This should do it in the Where clause for your query.

WHERE [Forms]![frmBrokerageCommissions]![RepNo]=&quot;061&quot; AND
tblRepAccountNo = &quot;ABC123456&quot; Or Forms!frmBrokerageComissions!RepNo <> &quot;061&quot; And tblRepAccountNo <> &quot;CBA123456&quot;


Paul

 

[PaulBricker]

hneal98, Not returns the same problems as <>.
Thanks for the thought.

Paul

 

[mkov]

I had already tried NOT, and that didn't work.

Paul, I will try that and let you know what happens.

Thanks for the help.

 

[mkov]

Paul, that didn't work either.

I appreciate everyone's help. I guess I will just leave it as 2 seperate queries.

Thanks again.

 

[Bchemist]

It would always be possible to build the query on the fly via VBA. This would allow you to change the criteria based upon a logical If...Then...ELSE statement.

 

[PaulBricker]

That should have done it. It worked for my test data. Can you post the sql for the query with the one that doesn't work.

Paul