Using the Like Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using the query by form technique, where-by i select options from
multiple combo/text boxes on a form which then acts as criteria for a query
(which feeds from only one table).

I have been using the following formula, so that if and option is chosen in
the text box then the query searches only for those records, but if the combo
is left blank is searches for all.

Like [forms]![Memb query]![Postcode] & "*" OR [forms]![Memb
query]![Postcode] Is

This works fine. However I need about 20 or so search criteria, each with
the above formula. Now this works but is sometimes causing the query to
crash, but more importantly the query is so large i can no longer open the
query in design view (except the SQL view) nor can i change (ie add new
fields) the query if i need to later down the line.

So, is there a more efficient way of doing this kind of search .... i'm
really stuck.

Cheers
 
sdgray said:
Hi,

I am using the query by form technique, where-by i select options from
multiple combo/text boxes on a form which then acts as criteria for a query
(which feeds from only one table).

I have been using the following formula, so that if and option is chosen in
the text box then the query searches only for those records, but if the combo
is left blank is searches for all.

Like [forms]![Memb query]![Postcode] & "*" OR [forms]![Memb
query]![Postcode] Is

This works fine. However I need about 20 or so search criteria, each with
the above formula. Now this works but is sometimes causing the query to
crash, but more importantly the query is so large i can no longer open the
query in design view (except the SQL view) nor can i change (ie add new
fields) the query if i need to later down the line.

So, is there a more efficient way of doing this kind of search .... i'm
really stuck.
Click to expand...

You need to "move to the next level" which is to build your query dynamically in
code. In its more basic approach you have a SQL string that represents the
basic SELECT statement with either a WHERE clause that will always be in affect
for your needs or one that does nothing (WHERE 1=1), for example. Then your
code examines each of the criteria option controls on your form and (for those
that are filled out) you build an additional AND clause that you tack onto the
end of the base SQL string.

When your code is finished you end up with a SQL statement that only mentions
the criteria choices that were actually filled out. Unused ones are simply
ignored. This results in a much simpler statement that will run much more
efficiently.

Here is a simple example. If I want to select from a Customers table and offer
choices for criteria that the user can fill out. I always want Customers who
are "Active" so my base SQL string looks like...

"SELECT * FROM Customers WHERE Active = True"

....which I keep in a variable named MySQL.

On my filter form I offer choices to filter on State, City, and Zip Code. The
controls on the form are thus named fltrState, fltrCity, and fltrZip
respectively. My code tests each one to see if it has an entry...

If IsNull(Me.fltrState) = False Then
MySQL = MySQL & "" AND State = '" & Me.fltrState & "'"
End If

If IsNull(Me.fltrCity) = False Then
MySQL = MySQL & "" AND City = '" & Me.fltrCity & "'"
End If

If IsNull(Me.fltrZIP) = False Then
MySQL = MySQL & "" AND ZipCode = '" & Me.fltrZIP & "'"
End If

CurrentDB.QueryDefs("NameOfQuery").SQL = MySQL

The last statement in the code applies the new SQL string to the stored Query
and then of course the report or form that used the query would be executed.

One can easily see the benefit of this approach as you offer more numerous
filtering options. Of course if you want to offer some OR options in addition
to ANDs then it gets considerably more complex, but the same thing is true with
your current form reference approach as well. Another nice feature is that once
you have set this up for current filter options, adding more at a later time is
accomplished with a few copy and pastes and then changing a few words.
 
Back
Top