Multiple search criteria

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi!

I'm trying to print reports based on criteria I enter in
two unbound text boxes on a 'Main Form', one for date,
the other for area code. If I type in a date only, I get
all Daily Jobs for that particular date. I then print
them out. If I enter an area code only, same again, all
Daily Jobs based on the entered area code. I then print
them out.

But what I want to do is specify a date AND an area code
and get all Daily Jobs that match the date and the area
code.

I'm trying to do it this way but to no avail.

The following VB code is attached to command button on
the main form that opens the Daily Jobs form.

stDocName = "Daily Jobs"
stLinkCriteria1 = "[Visit Date]=" & "#" & Me![Date] & "#"
stLinkCriteria2 = "[Area Code]=" & "'" & Me![Area] & "'"
DoCmd.OpenForm
stDocName, , , "[stLinkCriteria1]", "[stLinkCriteria2]"

As this doesn't work what am I doing wrong may I ask?


Thank you for your help

aj
 
Try:

stDocName = "Daily Jobs"
strWhere = ""

if isnull(me![Date]) = False then
strWhere = "[Visit Date] = " & "#" & Me![Date] & "#"
end if

if isnull(me![Area]) = false then

if strWhere <> "" then
strWhere = strWhere & " and "
end if

strWhere = strWhere & "[Area Code]=" & "'" & Me![Area] & "'"

end if

DoCmd.OpenForm stDocName, , , strWhere

You can see how we can actually add MORE conditions to the screen. You
simply just keep building up the strwhere for as many conditions as you
need!

I will suggest a few general tips and hits here:

Try and avoid "date" as a field name, as it is also a function in
ms-access and it can on occasion get access confused.

Try and avoid spaces in field names, again, this can be a source of
problems. Many sql database systems don't allow spaces. While ms-access
does, if you ever need to export the data to another system, you can run
into problems. You might as well make this a habit.


I also would suggest you create a report to send out the data, and not
use a form for printing. (the "where" clause will work with reports also).
The advantage of the report is your users can preview it before printing
(this allows changing printers etc). You can also have things like email, or
even convert to word docs. You also have much better layout and printer
control.

I am really a big fan of your idea of creating prompt forms. I also use them
all the time, and they are very user friendly.

Here is some screen shots of some I have done:
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
 
Back
Top