By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

How to show all values in a query iif criteria?

100+
P: 176
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.
Jan 7 '08 #1
Share this Question
Share on Google+
14 Replies


Megalog
Expert 100+
P: 378
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.
Jan 7 '08 #2

jaxjagfan
Expert 100+
P: 254
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())
Jan 7 '08 #3

100+
P: 176
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?
Jan 7 '08 #4

jaxjagfan
Expert 100+
P: 254
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.
Jan 7 '08 #5

100+
P: 176
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.
Jan 8 '08 #6

FishVal
Expert 2.5K+
P: 2,653
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
Jan 8 '08 #7

jaxjagfan
Expert 100+
P: 254
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. Dim vCustType
  3. vCusttype = Me.MyInputControl
  4. If vCustType < 3 Then
  5. strSQL = "SELECT * FROM [YourTable] WHERE [CustomerType]=" & vCustType & ";"
  6. Else strSQL = "SELECT * FROM [YourTable]"
  7. End if
  8. Docmd.RunSQL strSQL
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 :

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl] OR Not In (1,2);
Jan 8 '08 #8

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [YourTable] WHERE [CustomerType]=[Forms]![MyForm]![MyInputControl] OR [Forms]![MyForm]![MyInputControl]=3;
  2.  
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
Jan 8 '08 #9

100+
P: 176
Thanks both of you.

Why does actually
Expand|Select|Wrap|Line Numbers
  1. OR [Forms]![MyForm]![MyInputControl]=3;
allows all the records to show when MyInputControl = 3?
Jan 8 '08 #10

100+
P: 176
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?
Jan 8 '08 #11

jaxjagfan
Expert 100+
P: 254
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.
Jan 8 '08 #12

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. [CustomerType]=[Forms]![MyForm]![MyInputControl] OR [Forms]![MyForm]![MyInputControl]=3
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. True OR True = True
  2. False OR True = True
  3. Null OR True = True
on the other hand if the second expression is False then the whole expression will be:
Expand|Select|Wrap|Line Numbers
  1. True OR False = True
  2. False OR False = False
  3. Null OR False = Null 
Hope this makes a sense.

Regards,
Fish
Jan 8 '08 #13

100+
P: 176
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.
Jan 9 '08 #14

P: 8
Actually, I seem to find the trick:
simply change your expression
Expand|Select|Wrap|Line Numbers
  1. IIf(fCustomerType()<>3,fCustomerType(), Like"*")
as:
Expand|Select|Wrap|Line Numbers
  1. like IIf(fCustomerType()<>3,fCustomerType(), "*")
you will see what your want!
Jan 15 '13 #15

Post your reply

Sign in to post your reply or Sign up for a free account.