Help with IIf and is null

[Rainman]

Hi,
I'm sure this is straight forward but can't seem to solve it.

I am trying to create in the query a criteria to say IIf [a condition is true] then return IS NULL, if false then do nothing.

I tried this but it doesn't work
IIf([Test]![Name]="Peter",Is Null,)

Can anyone help ?

Thanks in advance

 

[Alansidman]

If I were doing this, I would put your formula in a new field.

Test: IIf([Test].[Name]="Peter","Is Null","")

In the criteria for this new field put ="Is Null"

Alan

 

[Rainman]

Thanks, but I just tried to simplify the example.
I really want a number of columns I can filter using the criteria. Ie if it's Peter then see all the null results in column 1. If its bill then see all the null results in column 2 etc.

Under each column in the criteria I wanted to write an iif statement with the results showing nulls depending who's name is selected.

If Peter then null,else (no filter) in criteria of column 1
If bill then null,else ( no filter) in criteria of column 2
Etc.

Does this make sense ?

 

[jdraw]

No, it's not making sense.
Why not show some data as it is; and the data you'd like in the result?

 

[kipcliff]

Are you looking for all of the Null values associated with a given criterium, or are you trying to set values to Null?

 

[Rainman]

I just want to show null results in a column which matches up with a name.

Column 1 data belongs to Peter, column 2 bill etc.

So if Peter is selected I only see null values for him.

 

[Rainman]

I select the name from a combo box then the query shows me which values are null against the name.

 

[jdraw]

Are you working with a combo on a form, or do you have a lookup in your table??

I'm not sure if you are aware, but Is NULL is a special term in SQL.

 

[Rainman]

The combo is in a form.

Uniform I use just is null in just one of the columns I get the correct result but the name is variable so I only want the column which relates to the name to show is null results.

Really I just want to know how to write an iif statement using is null if true.

 

[RainLover]

You could use this as an additional field just to do some testing but it does not solve your problem completel

NewField(IIf([Test].[Name]="Peter",Null,[Name])

Do you have a column for each name?

Like this

Peter Fred Tom

or do you have

Peter
Fred
Tom.

Tested in Win 7 Access 2003

 

[Rainman]

Peter 1. Bill 1. Etc
Peter
Bill
Etc

If Peter then (is null), (no change). If bill then (is null), (no change)

 

[Alansidman]

I just want to show null results in a column which matches up with a name.

Column 1 data belongs to Peter, column 2 bill etc.

So if Peter is selected I only see null values for him.

This sounds like you may have a table which is not normalized and that is making your question difficult to resolve. What does your table look like? Can you show some lines of sample data so we have a better idea of what you are looking at.

Also, regarding normalization, look at this white paper.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx