How to omit a field that has zero values

[yus786]

Hi all

Trying to create a query from a table. This table has some fields with zero all the way down for all the records and i wish for the query to omit the whole field if that is the case

Can someone please advise how this can be done?

Thanks

 

[vbaInet]

Zeros across fields (i.e. columns) or across rows?

 

[yus786]

Zeros across fields (i.e. columns) or across rows?

Hi, yes fields (columns),

Thanks

 

[vbaInet]

You can use the IIF() function:
http://www.techonthenet.com/access/functions/advanced/iif.php

 

[yus786]

Thanks, not sure i'll be able to get my head round this but will have a look

Still unsure what 'Value' would be to omit the whole field

iif ([OT15] < 0, ???, [OT15])

The field is called OT15

Any idea what the above should look like?

Here is what the table looks like

 

[vbaInet]

Almost there! So if it's equal to 0 return Null, otherwise return the value.

 

[yus786]

iif ([OT15] < 0, null, [OT15])

This didn't work. Still shows the column

Also tried this

iif ([OT15] = 0, null, [OT15])

This return nothing at all ie no records

This does go into the Criteria right?

 

[vbaInet]

No it replaces the field in the query.

 

[yus786]

OK, so that removed all the zero's but still has the field

Any idea how to remove that?

 

[vbaInet]

Oh, if you don't want the field you can simply remove it from the SQL. You don't have to have all fields there.