Using a form to input query criteria

Topic

New Reply

Access 2007

I’m using a form to collect the criteria data for queries and it works when I input a single specific criteria. However, sometimes I need to query on all of the data in a field or just some of the data. When I leave any of the input fields blank, the query returns nothing. Some of the fields are numeric and some are text.

Reply | Quote

Replies

I just want to make sure I am clear about this.

Your form has a number of controls on it. If you fill them all in, the query which uses them all as criteria works.

But you want to be able to fill in just some of the controls, and then you want only the ones with data in them to be used as criteria.

If that is right, then the only solution I know is one that involves a lot of VBA code.

When you have a query with criteria, the criteria make up the “Where” clause of the SQL of the query.

In the situation you describe you need to build the SQL in code, then build the Where clause looking at each of the controls in turn and using the ones that are not null.

We can go further and look at what that VBA code would look like if you want.

Reply | Quote

I didn’t explain this well! The query works OK if fields on the form are left blank. My problem is when I want multiple criteria in a field.

Example: The form has a field for the ‘City’. When I enter one city name in the field it works fine. Sometimes I need to query on more than one city or all of the cities. How do I specify more than one city or all cities in the field on the form?

Reply | Quote

To be able to Search for one city OR another you need two text boxes.

The SQL for the query looks like this:

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb]));

For an OR query it needs to look like this

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb1]) or ((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb2])) ;

One option is a multiselect list box, listing the towns you have. But this requires VBA code. It can’t be done with a simple query.

You can tell the query to treat an empty text box to mean All Cities.

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb])) or   OR ((([Forms]![frmTestSearch]![txtsuburb]) Is Null));

That looks like this in the query grid.
30615-Citiesquery

Really flexible searching generally requires using VBA to write a Where clause

Reply | Quote

I have worked on some Access databases where the original developer used some pretty complex queries generated by the query builder.

So while VBA is not the only way, IMO VBA is the must way to go for simplification of debugging.

Cronk

Reply | Quote