Only show certain fields depending on condition - how?

[scy90uk]

I'm a bit stuck and fairly creeky with Access.

I have a table whereby I need certain fields NOT to display in the query results if another field (in the same row) has a particular value i.e. if chocolate = "n" don't display field "chocolate type". I need to do this for 3 other fields i.e. if Field A = N, don't display Field B on the same row.

I've tried simple query, which worked in the sense it only returned rows that satisfied all of the queries on a particular row. I need all the records in the table to be returned minus the fields have been queried.

Any suggestions on how I might be able to do this are gratefully received. Is it possible to do this as a query?

Hope this makes sense.

 

[Remou]

You can use IIf:

[tt]ATitle: IIf([Chocolate] = "Y",[ThisField],"N/A")[/tt]

 

[scy90uk]

Thanks Remou, but it only returns some of the results. I was hoping that if field A said field B could not be displayed - it would replace the initial value with a blank value in a new table, so that all the records in the table would be returned but with blank fields where the information needs to be omitted. Any suggestions?

Thanks.

 

[lespaul]

I think you need a UNION query

SELECT Field1, Field2, chocolate As FieldToShow FROM tableName where Chocolate = "Y"
UNION
SELECT Field1, FIeld2, Coconut FROM tableName WHERE Coconut = "Y"
UNION
SELECT Field1, Field2, Nuts From TableName WHERE Nuts = "Y"

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[scy90uk]

Thanks LesPaul, I've just tried this, but the query returned the same rows twice.

I'm wondering if there is a way to query the fields necessary and hold each of the results in an individual variable each which is then put into a select query?

For example:

IF Field x = "Y" display Field B ELSE change Field B to a blank result

IF Field z = "Y" display Field c ELSE change Field c to a blank result

(Not sure how to code write the above in SQL)

$mainquery = SELECT Field A(), Field B( Field B value from above), Field C ( Field C value from above) from tablename (to create the list).

Results would be:

Field A | Field B | Field C | Field X | Field Z

Myname Myaddress Mynumber Y Y
Myname [blank] Mynumber N Y
Myname Myaddress [blank] Y N


Any help in how to code this logic (if possible and if right!) would be very much appreciated.

Thanks.

(PS I'm also wondering if a sub select query would work(?), all my records are in one table though.)

 

[lespaul]

SELECT A, B, C, iif(B <> "", "Y", "N") As X, iif(C <> "", "Y", "N") As Z
FROM TableName

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com