Iff Statement in Criteria (1 Viewer)

[dgambale@gmail.com]

Hello,

Im trying to develop certain criteria in a query and having some issue adding operators to the IFF statement.

In the criteria section of the query builder I have the following:

IIf([Forms]![Vendor Query]![cmbMinSize]="#2", "#2 Or #0","*")

I have also tried less complex as I thought the hashtag was messing things up

IIf([Forms]![Vendor Query]![cmbMinSize]="#2", "PA Or Ca","*")

This does not work either, however if I just have one term in true side of the expression it works fine.

IIf([Forms]![Vendor Query]![cmbMinSize]="#2", "PA","*")

Is there a way to develop the true side so I could have essentially a criteria with many OR values. Like PA or CA or NY....

Thanks for your help

 

[CJ_London]

suspect you are trying to use a query in a way that will not work. What is the full criteria you are trying to use?

Also, be aware that # has a special meaning in sql (a string between two # will be interpreted as a date), so may also be causing an issue

 

[dgambale@gmail.com]

To boil it down to the simplest thing. I want the IIF statement to return a search criteria equal to the "CA or PA" for example. Taking out the hash tags, I cant get a simple "OR" statement to work in the IFF function. Any ideas?

 

[Frothingslosh]

You don't use IIf in criteria.

As a rule, if you want to return all records with "PA" or "CA" in a certain field, you would enter the following into the criteria box for that field in the QBE grid:

"CA" Or "PA"

If you're looking for something more involved than that, then my first suggestion would be to tell us, preferably in plain English since we don't know your application, design, and requirements, what precisely you are trying to do. It sounds like you're going to need multiple criteria rows in the QBE grid, but I'm not 100% certain.

 

[CJ_London]

you cannot create a criteria in the way you are trying to. The criteria would need to look like

WHERE somefield='CA' or somefield='PA'

The other way is

WHERE somefield IN ('CA','PA')

so you could try

WHERE somefield IN ([Forms]![Vendor Query]![cmbMinSize])

but I don't think it will work

Forget about your form for a moment. Create your query and in the criteria one by one put all the different ways you want the user to be able to filter the data. Make a note of how each of these are constructed.

That is what you will need to construct in your form.

 

[CJ_London]

@Frothinslosh

if you put

"CA" Or "PA"

in the criteria row of the query grid, it will translate in SQL to

WHERE somefield='CA' or somefield='PA'

 

[Frothingslosh]

@Frothinslosh

if you put

"CA" Or "PA"

in the criteria row of the query grid, it will translate in SQL to

WHERE somefield='CA' or somefield='PA'

I know that. Note that my comment using the QBE grid was posted before your comment (#5) ever went up. It's not an attempt to correct you if mine was up 2 minutes earlier.

Also, I used the QBE grid as an example instead of SQL text because most people asking for help along this line, from what I've seen, tend to use it rather than typing up the SQL statement. The OP's attempt to put an IIf into the criteria block (and his reference to criteria rather than a WHERE clause) suggests to me that he was one of them.

No need to confuse him on writing a SQL statement if he's using the QBE.

Edit: In fact, based on the original post, I think we're looking at a

WHERE ((A = X) And (B = Y Or B = Z)) Or ((A = L) And (B = M Or B = N))

situation.

 

[sneuberg]

You can't write expression like for example:

If x = 2 Or 3 then

The x needs to be repeated like

If x = 2 Or x = 3 Then

Since in a case like yours where you need to repeat the field I find it easier just to switch to SQL view and write a boolean expression in the WHERE clause. Let's say your field is named State then you could write a WHERE clause like.

WHERE ([Forms]![Vendor Query]![cmbMinSize]="2" And ( [State] =  "PA"  Or [State] = "CA")) Or ([Forms]![Vendor Query]![cmbMinSize] <> "2" And [State] Like "*")

Edit: I've attached the database where I tested the expression

 

[Frothingslosh]

Heh...I believe that out of the three of us, Steve gave the best answer.