Change Query Based On Form Fields

[NPhillips]

I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.

I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.

However, if one of the fields is blank, the query doesn't return anything.

Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.

It's not an error-checking issue, either, as entering info into all of the fields isn't required.

For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.

A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.

There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).

I hope I'm clear in what I'm trying to accomplish

Thanks in advance!

 

[neileg]

This has been covered many times. Try searching these forums.

 

[Moniker]

You can always just use a null zero function to covert blanks into asterisks so that all fields get returned. It would look like this in a query:

SELECT
    Nz([AgentName],"*")
    ,Nz([BeginningDate],"*")
    ,Nz([EndingDate],"*")
    ,Nz([RequestType],"*")
    ,Nz([DistrictName],"*")
FROM
    YourTableName
;

Search on the Nz function if you're not familiar with what that's doing.

~Moniker

 

[EMP]

I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.

However, if one of the fields is blank, the query doesn't return anything.

See Jon K's Basic Criteria Format in the Sample Database forum:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[Wiz47]

I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.

I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.

However, if one of the fields is blank, the query doesn't return anything.

Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.

It's not an error-checking issue, either, as entering info into all of the fields isn't required.

For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.

A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.

There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).

I hope I'm clear in what I'm trying to accomplish

Thanks in advance!

SELECT YourTable.*

FROM YourTable

WHERE (([YourFieldInTable]=Forms!YourFormName!YourtxtField Or Forms!YourFormName!YourtxtField Is Null)=True)

Simplest way to do it that I know. This allows for "blank" fields when doing a query from a form.

 

[NPhillips]

Thanks for the responses, everyone.

After neileg's response, I pushed harder with my searching, and found a method that works, using a bunch of conditionals and building a filter string from that.

But, I think JonK's sample DB that EMP linked might be a bit smoother. I'll have to play with it when I have time.

Thanks again, and Happy New Year!

 

[grnzbra]

You could loop through the fields collection of the criteria input form and create a WHERE clause to include any input data and ignore any fields not containing anything. Then tack this onto the SQL statement for the query and set the query's SQL property to the SQL statement you just constructed.