Parameter Query based on Form


B

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
 
Ad

Advertisements

J

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)
 
Ad

Advertisements

B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top