How to show all values in a query iif criteria?

I'm also interested in a good answer to this, since I came upon the same problem a few days ago. I had an IIF statement in a query Criteria field, and it just wouldnt work no matter which way I arranged it. After a few hours of messing around with it, I ended up re-working my form to get around the query.

Hello forum!

I have this question:

I have a query with a column that uses this criteria:

Expand|Select|Wrap|Line Numbers

1. (IIfCustomerType()<>3,fCustomerType())

where fCustomerType() is a function result. But this criteria isn't enough because I also need a proper else condition that will show all the results. How do I write it?... I tried:

Expand|Select|Wrap|Line Numbers

1. IIf(fCustomerType()<>3,fCustomerType(), Like"*")

, but it doesn't work

Thank you.

You need to replace the "Like *" with a function that returns results not the criteria of the function. If fCustomerType() returns a value if customer is "<>3" then you need a similar function to return a value when customer "=3".

Look at the fCustomerType() function and possibly create a 2nd one based on the 1st one or post it here for us to see. Can't be more definitive with more info.

IE - IIf(fCustomerType()<>3,fCustomerType(),fCustomerTy pe3())

You need to replace the "Like *" with a function that returns results not the criteria of the function. If fCustomerType() returns a value if customer is "<>3" then you need a similar function to return a value when customer "=3".

Look at the fCustomerType() function and possibly create a 2nd one based on the 1st one or post it here for us to see. Can't be more definitive with more info.

IE - IIf(fCustomerType()<>3,fCustomerType(),fCustomerTy pe3())

Thanks.

In my application, If fCustomerType()=1 then the query should show only the records with '1' values, if fCustomerType()=2 it should show only the records with '2' values, if fCustomerType()=3 it should show all records.

By what value of fCustomerType3() should I represent all values?

Thanks.

In my application, If fCustomerType()=1 then the query should show only the records with '1' values, if fCustomerType()=2 it should show only the records with '2' values, if fCustomerType()=3 it should show all records.

By what value of fCustomerType3() should I represent all values?

If I am reading you reply correctly, you are not looking for the "false" side of an "IIF" function. What you need is a "WHERE" clause.

Select tblCustomers.* From tblCustomers WHERE CustomerType = [Forms]![YourFormName].[YourInputControl]

What are you trying to accomplish? Are you displaying the data in a report or selecting data for data entry? How are you controlling what CustomerType is selected - ComboBox, TextBox, etc.

The IIF whould normally be used if you were calculating or doing something different for each or a particular CustomerType. IE - Tax:IIf(customertype=3,0, taxrate*amount) This would imply all type 3 were tax-exempt and tax all others.

Select tblCustomers.* From tblCustomers WHERE CustomerType = [Forms]![YourFormName].[YourInputControl]

What are you trying to accomplish? Are you displaying the data in a report or selecting data for data entry? How are you controlling what CustomerType is selected - ComboBox, TextBox, etc.

The [CustomerType] values I have in tblCustomers are either 1,2,11 or NULL.

I want to write a query that selects either:

1. Only records with [CustomerType] value 1.
2. Only records with [CustomerType] value 2.
3. All the records availible.
and all this depending on the [Forms]![MyForm]![MyInputControl] which can be 1,2 or 3

I successfully acomplish selecting records with [CustomerType] value 1 or [CustomerType] value 2 with your query, but I can't get to select all records availible.

Regards.

Hi, Michael.

Sounds to be trivial.

Regards,
Fish

Hi, Michael.

Sounds to be trivial.

Expand|Select|Wrap|Line Numbers

1. SELECT * FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl] OR IsNull([Forms]![MyForm]![MyInputControl]);
2.  

Regards,
Fish

If you are using 1, 2, and 3 as choices and the possible return values are 1, 2, and ALL.

In essence it is 2 different queries where one looks for customertype 1 or 2 and the other pulls all records regardless of customertype. If you want all but 1 or 2 when you select 3 then change Fish's reply :

If you want all but 1 or 2 when you select 3 then change Fish's reply :

SELECT * FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl] OR Not In (1,2);

Actually, if so, I would use
But it seems me more intuitive to fetch all records if no filter is specified in [Forms]![MyForm]![MyInputControl] ([Forms]![MyForm]![MyInputControl] is Null).

Regards,
Fish

Thanks both of you.

Why does actually allows all the records to show when MyInputControl = 3?

In other words, can you explain why exactly this query works?
I changed * with Column1 for the convenience of the my question.

I get this part:
I select records where customertype has a specific value


Or where MyInputControl = 3. But why does it selects all the records?

In other words, can you explain why exactly this query works?
I changed * with Column1 for the convenience of the my question.

I get this part:

Expand|Select|Wrap|Line Numbers

1. SELECT [Column1] FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl]

I select records where customertype has a specific value

Expand|Select|Wrap|Line Numbers

1.  OR [Forms]![MyForm]![MyInputControl]=3;

Or where MyInputControl = 3. But why does it selects all the records?

Because the WHERE clause is not looking at CustomerType anymore - it is looking for a value of 3 in MyInputControl and selecting all.

In other words, can you explain why exactly this query works?
I changed * with Column1 for the convenience of the my question.

I get this part:

Expand|Select|Wrap|Line Numbers

1. SELECT [Column1] FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl]

I select records where customertype has a specific value

Expand|Select|Wrap|Line Numbers

1.  OR [Forms]![MyForm]![MyInputControl]=3;

Or where MyInputControl = 3. But why does it selects all the records?

Just would like to rephrase jaxjagfan's answer.

All you've entered in WHERE clause of SQL expression is being evaluated as a boolean expression in context of a record being fetched. If the expression evaluates to True, then the record is fetched, otherwise it is omitted.

So you have a simple boolean equation
Two boolean expressions joined by boolean operator OR. If either of them is True, then the whole expression is True. The first expression is record dependant - it may be False, True or Null for a particular record in the table. The second is record independant - it is being evaluated once for all records.
It is obvious that if the second expression is True then the whole expression will be True for all records:
on the other hand if the second expression is False then the whole expression will be:
Hope this makes a sense.

Regards,
Fish

Yes, I understand now it is possible to write a record independent expressions, but expressions that still makes sense to access query, such as access function.

Thanks guys for you help and explanation.
Regards.

Actually, I seem to find the trick:
simply change your expression
as:
you will see what your want!

@imahhua
Thank you imahhua

USE THE LIKE before the IIf statement. Like this.

Like IIf([Forms]![FormName]![FraFilter]=2,[Forms]![FormName]![txtNumber],"*")