Where clause?

[Benny Wong]

Hello All,
I am using Microsoft Access 2000. I have already created the data entry
form for the user to input data.
now that the data is entered into the database my boss wants me to create a
front-end interface. This
interface I guess is called a Query-by-form I think? I have created the form
which contains the following:

ContactType = Listbox - multiselect
AgencyType = Combobox - single-select
City = Combobox - single-select
County = Combobox - single-select
Region = Combobox - single-select
Source = Combobox - single-select
Client Status = Combobox - single-select
Practice Group = Combobox - single-select
Zip Code = Unbound Textbox - using "between"
range on zipcode

The above listed items Listbox, Combobox, and Unbound Textbox when the user selects the respective items builds the where clause or clauses. I do understand how to create a Parameter Query, but I do not understand how to do for multiple combination of Listbox, Combobox, and Unbound Textboxes to
create a report. I would appreciate if someone could explain in simplistic form the concept of creating this type of where clauses and to create my report. Thanks in advance for your generous time and consideration in assisting me.

 

[jjturner]

Hi Benny,

Since your search criteria consists of many fields which may (or may not) be populated, you won't be able to use a saved query to run your report. (If the user DOESN'T have discretion as to whether a control is left blank and has to enter a value for each one, then you can use a saved parameter query.) As you've alluded to, you'll be building your query definition (the WHERE clause) at run-time and you'll have to use VBA for this.

The main function of the VBA code will be to examine each of your controls and test whether the user has entered a value for that control. If so, the code will add that piece of criteria to your WHERE clause, which is stored in a string variable. The string variable is then used as part of an SQL statement in a query definition.

The trigger to run the code is usually when the user clicks a command button (i.e. - 'Run Report') on the form.

The whole process actually isn't too complicated. I'm assuming that you haven't done much with programming in VBA - I apologize if that's not the case. However, it sounds like learning how to program is where you're headed with your job responsibilities.

Hope this helps out in some way!

Regards,
John