IIf Query Question

[Phizzle]

I have a table w/ several check boxes and I would like to run a query against it, but I don't want to see the ones that are false/no - I think I can do this w/ the IIf function (eg. store each True as new column and supress the False?), but I don't know how - any help would be greatly appreciated.

Thanks in advance!

 

[Melagan]

If you don't want to see records where your checkboxes are false, why not just add something like:

WHERE your_chk_box = 0

..or put 0 in your criteria box in query design mode for your checkboxes.

Usually a checkboxes numeric value for true is 0 and -1 for false.

Incidently, you could just query for "true" or "false" instead of using the numeric values.

Why do you think you need to emplor an IIF statement? Maybe some sample data and expected results would help if it's more complicated than this.

~Melagan
______
"It's never too late to become what you might have been.

 

[Phizzle]

This particular table has about 12 checkbox columns. I only want to disply those that are checked (True). If I do a normal query I will get the rows that pass the criteria, but I'll still see all the columns that were false. Not sure if that makes sense...

Code
SELECT tblCAP.ProviderName, tblCAP.Street, tblCAP.City, tblCAP.State, tblCAP.ZIP, tblCAP.County, tblCAP.ContactPersonFirst, tblCAP.ContactPersonLast, tblCAP.ContactPhone, tblCAP.ContactFax, tblCAP.ContactEmail, tblCAP.Comments, tblCAP.Corp_Office, tblCAP.Checkbox1, tblCAP.Checkbox2, tblCAP.Checkbox3, tblCAP.Checkbox4, tblCAP.Checkbox5, tblCAP.Checkbox6, tblCAP.Checkbox7, tblCAP.Checkbox8, tblCAP.Checkbox9, tblCAP.Checkbox10, tblCAP.Checkbox11, tblCAP.Checkbox12, tblCAP.Checkbox12
FROM tblCAP
WHERE tblCAP.ProviderName="Whatever";

-----------
This gives me the rows, but I want to "hide" the columns that are not true. I thought I could do this w/ the IIf statement against those fields that I want to verify.

Thanks again!

 

[lespaul]

no you can't "hide" fields unless you don't include them in the select statement. The root of the problem you're having is that your table isn't normalized.


Leslie

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

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[randy700]

Let's disregard all the extra data in the query and see if I understand your question. The results you currently get might look like this?
NAME CB1 CB2 CB3 CB4 CB5 CB6 etc
JOHN YES YES YES YES NO YES
FRED YES NO YES YES YES NO

You want to see this...
NAME CB1 CB3 CB4 CB6 etc
JOHN YES YES YES YES
FRED YES YES YES YES

If I'm right, you need to add the WHERE clause to each checkbox field.
WHERE CB1=0 AND CB2=0 AND... etc


Randy

 

[Phizzle]

Randy - that's exactly what I'm after - I've been able to do it w/ the IIf and multiple queries, but doesn't seem practical. Could you elaborate a bit on your suggestion?

Thanks again,

 

[randy700]

SELECT tblCAP.ProviderName, tblCAP.Street, tblCAP.City, tblCAP.State, tblCAP.ZIP, tblCAP.County, tblCAP.ContactPersonFirst, tblCAP.ContactPersonLast, tblCAP.ContactPhone, tblCAP.ContactFax, tblCAP.ContactEmail, tblCAP.Comments, tblCAP.Corp_Office, tblCAP.Checkbox1, tblCAP.Checkbox2, tblCAP.Checkbox3, tblCAP.Checkbox4, tblCAP.Checkbox5, tblCAP.Checkbox6, tblCAP.Checkbox7, tblCAP.Checkbox8, tblCAP.Checkbox9, tblCAP.Checkbox10, tblCAP.Checkbox11, tblCAP.Checkbox12, tblCAP.Checkbox12
FROM tblCAP
WHERE tblCAP.ProviderName="Whatever"
AND tblCAP.Checkbox1 = 0
AND tblCAP.Checkbox2 = 0
AND tblCAP.Checkbox3 = 0
AND tblCAP.Checkbox4 = 0
AND tblCAP.Checkbox5 = 0
AND tblCAP.Checkbox6 = 0
AND tblCAP.Checkbox7 = 0
AND tblCAP.Checkbox8 = 0
AND tblCAP.Checkbox9 = 0
AND tblCAP.Checkbox10 = 0
AND tblCAP.Checkbox11 = 0
AND tblCAP.Checkbox12 = 0


Randy

 

[lespaul]

so if a single person has a NO in the field you don't want to show the field at all?

In the example data given above, John has a NO in CB5 but Fred has a YES and the opposite on CB2. The result set does not show CB2 or CB5 at all, but one of the people had a YES in that field.


Leslie

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

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[randy700]

It appears that he wants only a list of people who have all yes data in all fields.


Randy

 

[Phizzle]

I got it to do what I need with:
services: IIf(IsNull([service1]),""," " & [service1]) & IIf(IsNull([service2]),""," " & [service2]) & IIf(IsNull([service3]),""," " & [service3]) & IIf(IsNull([service4]),""," " & [service4]) & IIf(IsNull([service5]),""," " & [service5]) & IIf(IsNull([service6]),""," " & [service6]) & IIf(IsNull([service7]),""," " & [service7]) & IIf(IsNull([service8]),""," " & [service8]) & IIf(IsNull([service9]),""," " & [service9]) & IIf(IsNull([service10]),""," " & [service10]) & IIf(IsNull([service11]),""," " & [service11]) & IIf(IsNull([service12]),""," " & [service12]) & IIf(IsNull([service13]),""," " & [service13])

But I have one more question - how do I not show the last comma?

Thanks!

 

[randy700]

Please post an example of the OUTPUT you desire.


Randy

 

[Phizzle]

Ok -
Currently I get:
Services:
Service1, Service2, Service3,

I'd like to only see
Services:
Service1, Service2, Service3

Basically the same thing minus the trailing comma.

Thanks!

 

[randy700]

Using the string that holds your result...

Services = Left(Services, (Len(Services)-1))


Randy