Parameter Query based on Form

[Bruce]

I have a select parameter query that uses a form for the selection criteria.
I want to be able to leave the field null on the form which in turn will
select all the records for that given field. I have tried various Iif
statements to no avail. This statement:

IIf([Forms]![Selection].[Class] Is Null,>="0",[Forms]![Selection].[Class])

produces no selected records when the [Forms]![Selection].[Class] is null.
When the field is not null it produces the desire results. I suspect part of
the problem is the greater than and equal sign.

Can anyone direct me on the correct way to write the selection criteria?

Bruce

 

[John W. Vinson]

I have a select parameter query that uses a form for the selection criteria.
I want to be able to leave the field null on the form which in turn will
select all the records for that given field. I have tried various Iif
statements to no avail. This statement:

IIf([Forms]![Selection].[Class] Is Null,>="0",[Forms]![Selection].[Class])

produces no selected records when the [Forms]![Selection].[Class] is null.
When the field is not null it produces the desire results. I suspect part of
the problem is the greater than and equal sign.

Can anyone direct me on the correct way to write the selection criteria?

Bruce

WHERE [fieldname] = [Forms]![Selection].[Class] OR
([Forms]![Selection].[Class] IS NULL AND fieldname >= 0)

If fieldname is a Text field then include the quotes around 0, if it's numeric
then omit them. If you in fact want to retrieve all records then it's simpler:

WHERE [fieldname] = [Forms]![Selection].[Class] OR
([Forms]![Selection].[Class] IS NULL)

 

[Bruce]

John,

Thank you very much! I have been racking my brain on this one and now that I
see the solution, I really feel dumb for not thinking of that, thank again.