This article focuses on a simple Classic Access Technique called Query By Form and it is written with the idea that you don't need to write masses of code to build a clever Access application, you just need to sprinkle a little code here and there. The simple QBF is then extended to the next level in what I call Query By Where.

So if you do not consider yourself a programmer then this should be right up your street, although it will extend you into the realm of sprinkle programming. There are quite a few steps here, and it will take you probably 20 minutes to do it yourself, but once you can do it you will start to see some of the real power behind Access.

Query By Form

When I first started with Access Version 2, this method of linking a form to a query to a report and then to a macro was an incredibly innovative idea. The method still holds and leads you on nicely to more sophisticated techniques, although I have left out the macro side of things in favour of Visual Basic For Application Programming. This approach is often called Query By Form. It shows what can be done with little or no programming.

We start with the requirement to generate a report listing customers in a specific country.

First create a form in design view, and add a text control to the form.

By displaying the fields properties we have then changed the default control name from Text0 to txtCountry (this is important because Text0 has little meaning). Save the form with the name frmCountry.

Next create a new query in design view adding the Customer table to the query. Select some fields including the field called Country. Then click in the criteria box below the country field and Right Click to select the Build option.

In the builder make the selections shown. then double click on txtCountry to display the value shown. Then press ok.

This cryptic looking language is in fact not to complicated, you can understand it only once you appreciate that the word Forms, means all loaded forms (the collection of all loaded or open forms).

So for all loaded Forms, find the form called frmCountry, then look for the control called txtCountry on that form.

Now save your query as "qryCountry".

Open the form and type in spain for the country (then press the enter key - this is important as it saves the value in the control), then open the query.

If you got this to work, then you are in a position to really get going.

If you now create a report in Access called rptCountry, based on the query qryCountry and open the form, type in a value, then open the report you will see the report on displays the required records.

The final touch is to add a command button to to the form using the Wizard to do this for us.

You can now spend you time making it all look pretty.

Top of Page

Working With Multiple Selections

If you extend the above technique to use several controls. You will come up against the problem that values must be supplied in each control. Otherwise a value of NULL gets put into the query, and it fails to work. There is a solution to this.

The criteria formulae is shown below. The IIF (Immediate IF) function conditionally either displays the required criteria, or enters the field name [Country] (and because this is always true the criteria then does not apply).

IIf(IsNull([Forms]![frmCustomer]![txtCountry]),[Country],[Forms]![frmCustomer]![txtCountry])

The advantage of this technique is that you don't need to use any program code, the disadvantage is that the queries start to become rather ugly and mistakes can easily be made.

Top of Page

Query By Where

One limitation on the above technique is once you start to want to have multiple selections, say Country, Customer Rating and Date Registered. Now it can be done, but it involves a query which uses a lot of conditional logic with IIF functions (see above). So lets look at a better way to do this.

Make a copy of the form which I have called frmCustomer, and add some additional controls.

The controls are called txtCountry, txtRating, txtStartDate and txtEndDate.

Then right click on the command button and select "Build Event". This displays the VBA Code behind the button

Modify the line of code to that shown below, then close the form saving any changes : -

What we have added "[Rating] = " & txtRating is known as the Where Clause. It restricts the data Where the condition is met.

Now make a copy of your report rptCountry called rptCountryWhere, and change the record source of the report to be the Customer table.

Open the form, enter a Rating of 2 and press the button.

Top of Page

Getting Clever

Now we get to the clever bit. You need to have a little bit of code which knows which controls you have entered values into, and can build the appropriate Where clause.

To write such code you need to follow some rules.

• If the control does not have any value, then don't include it, test for NULL (no value).
• If more than one control has a value use the keyword AND to link the conditions together.
• You must apply a different syntax when it is a text or date field.

When it is a number the syntax is easy "[Fieldname] = " & Controlname

When it is a text field you must add a single quote ' at the beginning and a ' at the end, the one at the end is enclosed inside double quotes.

"[Fieldname] = '" & Controlname & "'"

When it is a date, you must surround the date with a # at the beginning and a # at the end, you must also reformat the date into US date format. This is because you are using the universal database language called SQL (Structured Query Language) and this standard is a US standard.

The code now looks like the following : -

Dim stDocName As String
stDocName = "rptCountryWhere"

Dim Whereclause As String

If Not IsNull(txtCountry) Then
Whereclause = "[Country] = '" & txtCountry & "'"
End If

If Not IsNull(txtRating) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Rating] = " & txtRating
End If

If Not IsNull(txtStartDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateRegistered] >= #" & Format(txtStartDate, "mm/dd/yyyy") & "#"
End If

DoCmd.OpenReport stDocName, acPreview, , Whereclause

You may have noticed that we are ignoring the end date in the above, if we want to test for both the start and end dates then we need a little more code.

The code can be downloaded by Clicking Here.

Top of Page

Top of Page