Report based on query not showing records with a blank field

[and]

Hi - I'm sure this is easy to sort out but I am a novice (still!).

I have a report based on a query (which just pulls together lots of fields from one table plus a field [Ethnicity] from another query). I have a command button on a form that opens the report for that particular record, but if I have left the Ethnicity field blank in the form (which I'd like to be able to do), the report does not show any of the fields at all. If I give Ethnicity a value, then all information shows up on the report.

How do I sort this out so that I can leave Ethnicity blank but still show all other info on my report?

TIA

Andrew.

 

[scriverb]

It sounds like you have a report with a query as its recordset that is selecting records based upon a textbox on your form. When you enter the appropriate entry in the forms textbox the specific type of records are selected. If left blank the selection is "No records".

To resolve use the following as the criteria example for the column that you are selecting on:

IIF(IsNull(FORMS![yourformname]![ethnicitycontrolname]), [Enthnicity], FORMS![yourformname]![ethnicitycontrolname])

What this expression does is check for the value in the forms control. If left blank it will return a Null value. If the value is null then select records an [Ethnicity] value equal to itself [Ethnicity] otherwise select based on the value in the forms text box.

Post back if you have any further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]