Query driven by form field

[Daren]

I'm trying to filter my query by a field on a form. The form field (combo
box) is using a distinctrow pull between two tables. The form name is
frmMain. The combo box is cboBroker. On the form, I'm displaying the
description if the field is null (which works). On my query, my statement is:

IIf([Forms]![frmMain]![cboBroker]<>"",[Forms]![frmMain]![cboBroker],([tblBrokerSalesExp]![BrokerCD]>=""))

If there is a value in the field, it works. If the field is null, then I
get no values. I've tried testing for a null value but get the same results.
Any ideas?

 

[John Spencer]

Try entering this as the criteria
WHERE ([Forms]![frmMain]![cboBroker] Is Null OR
[tblBrokerSalesExp]![BrokerCD]=[Forms]![frmMain]![cboBroker])

That should return all records if the control is null and matching
records if there is a value in the control.

If you enter that into a criteria "cell" in query design view, then
enter the following. Access will reformat this when you close and save
the query.

([Forms]![frmMain]![cboBroker] OR [Forms]![frmMain]![cboBroker] is Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================