Parameter Query To Pull Null Value

[Guest]

I have a parameter query that pulls Students based on graduation class text
field,i.e., 2004, 2005, etc. My parameter in query design view criteria is:

Like [Enter Class] & "*"

This works to pull students in individual classes or in all classes. How do
I pull students whose Class field is blank?

 

[Rick B]

I guess you could do an if statement. Something like (not tested)...

If ([Enter class or ! for blanks]="!",Is Null,Like [Enter class or ! for
blanks] & "*")



Agina, I have not tested this.

 

[Guest]

You could add a second parameter so it would read:
Like [Enter Class] & "*" Or Is Null

 

[Guest]

I have tried this. If I enter 2004, it returns 2004 and blank classes. I only
want it to return blank classes if the user specifically selects it.

Any ideas?

You could add a second parameter so it would read:
Like [Enter Class] & "*" Or Is Null

I have a parameter query that pulls Students based on graduation class text
field,i.e., 2004, 2005, etc. My parameter in query design view criteria is:

Like [Enter Class] & "*"

This works to pull students in individual classes or in all classes. How do
I pull students whose Class field is blank?

 

[Guest]

You could try to use an IIF in the criteria.

LIKE (IIF([Enter Class] is null, is null,[Enter Class] & "*"))

I didn't test this but you may get lucky with some version of this.

I have tried this. If I enter 2004, it returns 2004 and blank classes. I only
want it to return blank classes if the user specifically selects it.

Any ideas?

You could add a second parameter so it would read:
Like [Enter Class] & "*" Or Is Null

I have a parameter query that pulls Students based on graduation class text
field,i.e., 2004, 2005, etc. My parameter in query design view criteria is:

Like [Enter Class] & "*"

This works to pull students in individual classes or in all classes. How do
I pull students whose Class field is blank?

 

[John Spencer (MVP)]

Might I suggest you enter the following as the criteria under the Class field.

Like [Enter Class] & "*" Or ([Enter Class] is Null AND [Class] is Null)

Access will in its infinite wisdom rearrange this, but as long as you don't have
too many other criteria to apply in your query it should work.

You could even use one of the other suggestions and instead of testing to see if
[Enter Class] is null you could test for a specific value such as "!".

Like [Enter Class; ! for Blanks] & "*" Or ([Enter Class] = "!" AND [Class] is Null)

You could try to use an IIF in the criteria.

LIKE (IIF([Enter Class] is null, is null,[Enter Class] & "*"))

I didn't test this but you may get lucky with some version of this.

I have tried this. If I enter 2004, it returns 2004 and blank classes. I only
want it to return blank classes if the user specifically selects it.

Any ideas?

You could add a second parameter so it would read:
Like [Enter Class] & "*" Or Is Null



:

I have a parameter query that pulls Students based on graduation class text
field,i.e., 2004, 2005, etc. My parameter in query design view criteria is:

Like [Enter Class] & "*"

This works to pull students in individual classes or in all classes. How do
I pull students whose Class field is blank?