performing search with null values

[Guest]

I am trying to perform a search query with 4 fields: type(combo box),
assigned location (combo box), make (text box), model (text box), Now i am
trying to figure out a way to query user input from a form that will allow
the query to run even if there are null values in certain fields. I can get
it to work with requiring all the fields to enter a criteria but if one is
null it does not work. I would appreciate any ideas on this.

-alfred

 

[Guest]

Alfred,

A few questions:

1. Are your "blank" fields text or numeric?

2. how are your combo boxes being populated?

3. in regards to your results are you looking for all values when a field
is blank or are you just looking for blank values?

 

[Guest]

The text boxes are text, the combo boxes are being populated by using sql
distinct for the row source, and i actually want the query to skip over the
blank fields. For example if i search by type and assigned location then i
only want it to return values as if those were the only 2 criteria.

-Alfred

 

[Andy UK Access User Group]

I can suggest two soloutions here. The first involves a trick tha
does not require any code

On the query grid add your field to query say [Country], and assum
that the desired form field is [Forms]![Form3]![txtBox2

Then in the criteria add the followin

IIf(IsNull([Forms]![Form3]![txtBox2]),[Country],[Forms]![Form3]![txtBox2]

This does mean that your queries start to look a mess. But you don'
need any code

A more sophisticated method is to use the docmd.Openrepor
"ReportName",acViewDesign,,Criteri
from program code. This you test each control, and build up a strin
for the criteria to exclude any controls with no value

The second method is much better, but you need to take a bit of car
with the syntax in Criteria