Multiple parameters 1

[Dophia]

Hi Everyone:
I am having trouble specifying different criteria in a query. I want the user to be able to retrieve records from a table, using a form to specify different criteria and show the results in a report. Is it possible to specify criteria from different fields in a table? For example: the database is for an animal shelter. I want the user to be able to specify a date range, then what type of animal, the breed of animal, the colour of the animal, etc.

Any guidance would be appreciated.
Sophia

 

[dhookom]

I almost always use the Where Condition of the DoCmd.OpenReport method. Don't put any dynamic/changeable criteria in the report's record source query. Use the Command Button wizard to write code to open your report. Then modify the code to build a where condition like:

   Dim strWhere As String
   strWhere = "1=1 "
   If Not IsNull(Me.txtStartDate) Then
      strWhere = strWhere & " And [DateField] >=#" & _
         Me.txtStartDate & "# "
   End If
   'do the same for other criteria
   DoCmd.OpenReport stDocName, acPReview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane:
Thanks for the help. I tried your suggestion, but I am stuck on the following:

strWhere = strWhere & " And [DateField] >=#" & _
Me.txtStartDate & "# "

What does the "[DateField]" represent? I get an error message stating that the engine could not find the object. I changed [DateField] to the name in my table and me.txtStartDate to the name on my form. Do I need to specify the table name?
Sophia

 

[dhookom]

DateField and txtStartDate are my "generic" names which need to be replaced by your names.

Come back with information about your reports record source as well as field types and control names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: The form opens the report and has the following code on the "OK" command. The form's date fields are named "cboStartDate" and "cboEndDate". The report is based on a Query which is based on the table "tblAnimal". The date field in the tblAnimal is named [Arrival_Date]. The error is "The microsoft database engine could not find
the object 'And [Arrival_Date] > ......etc.

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String

If Not IsNull(Me.cboStartDate) Then
strWhere = strWhere & " And [Arrival_Date] >=#" & _
Me.cboStartDate & "# "
End If
If Not IsNull(Me.cboEndDate) Then
strWhere = strWhere & " And [Arrival_Date]<=#" & _
Me.cboEndDate & "# "
End If
'do the same for other criteria
DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, strWhere

If Not IsNull(Me.cboAnimalType) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.cboAnimalType & "' "
End If
If Not IsNull(Me.cboCatBreed) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.cboCatBreed & "' "
End If
End Sub

Can you direct me as to what the code should have?
Thanks, Sophia

 

[lespaul]

looks like your missing:
strWhere = "1=1 "

which needs to be set before you add any "AND ..." clauses.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

In addition to Leslie's comment, I would expect to see
[blue] DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, strWhere[/blue]
near the end of the code rather than in the middle.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Thanks for the help. I am having the following problems now:
1) When I add the ....strWhere = "1=1 ", I get an error..."The Microsoft Jet database engine could not find the object '1-1 And [Arrival_date]......
2) When I add the " DoCmd.OpenReport "RptAnimalInfo_Find" acPreview, strWhere " after the last "End If", I get the same error message, but to the error message, all my criteria are listed ....

Any suggestions?
Thanks Sophia

 

[Dophia]

Could the problem be that the report's query has the criteria parameters included in it?
Sophia

 

[dhookom]

Please post back with your full code. In 1) you mention both "1=1 " and "1-1 ". I also think are missing one of the commas in the DoCmd.OpenReport method.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

This is the code now:

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "


If Not IsNull(Me.cboStartDate) Then
strWhere = strWhere & " And [Arrival_Date]>=#" & _
Me.cboStartDate & "# "
End If
If Not IsNull(Me.cboEndDate) Then
strWhere = strWhere & " And [Arrival_Date]<=#" & _
Me.cboEndDate & "# "
End If

If Not IsNull(Me.cboAnimalType) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.cboAnimalType & "' "
End If
If Not IsNull(Me.cboCatBreed) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.cboCatBreed & "' "
End If
If Not IsNull(Me.cboDogBreed) Then
strWhere = strWhere & " And [StringField]>='" & _
Me.cboDogBreed & "' "
End If

DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, strWhere

End Sub

 

[Dophia]

If I click "ok" to the error message, the report shows the correct animal type and animal breed reports, but the date range is for the entire database and not what I specified the date range to be.
Sophia

 

[dhookom]

You are missing a comma

DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, ,strWhere

Since you are applying a where clause in the OpenReport method, as per my first post

Don't put any dynamic/changeable criteria in the report's record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Thank you Duane!
The error messages are gone now, but there is still a problem in that when I choose a specific dog breed on the form, the report shows all of the dog breeds. But when I specify a cat breed, it works correctly. Can you see what the problem might be? I have checked and rechecked the field names. I know I have spaces in the names, but it was set up that way.

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "


If Not IsNull(Me.cboStartDate) Then
strWhere = strWhere & " And [Arrival_Date]>=#" & _
Me.cboStartDate & "# "
End If
If Not IsNull(Me.cboEndDate) Then
strWhere = strWhere & " And [Arrival_Date]<=#" & _
Me.cboEndDate & "# "
End If

If Not IsNull(Me.cboAnimalType) Then
strWhere = strWhere & " And [Category_ID]>='" & _
Me.cboAnimalType & "' "
End If
If Not IsNull(Me.cboCatBreed) Then
strWhere = strWhere & " And [Cat Breed]>='" & _
Me.cboCatBreed & "' "
End If
If Not IsNull(Me.cboDogBreed) Then
strWhere = strWhere & " And [Dog Breed Main]>='" & _
Me.cboDogBreed & "' "
End If

DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, , strWhere

End Sub

 

[Dophia]

I just added another criteria to the form and it doesn't work either. It seems that it is only using the date criteia, the animal type criteria and the cat breed criteria, but nothing after that. Should there be more code to deal with the "cat breed" if it is null?
Sophia

 

[dhookom]

You should be using "=" rather tha ">=" which doesn't make sense

If Not IsNull(Me.cboAnimalType) Then
    strWhere = strWhere & " And [Category_ID] = '" & _
      Me.cboAnimalType & "' "
End If
 If Not IsNull(Me.cboCatBreed) Then
    strWhere = strWhere & " And [Cat Breed] = '" & _
      Me.cboCatBreed & "' "
End If
 If Not IsNull(Me.cboDogBreed) Then
    strWhere = strWhere & " And [Dog Breed Main] = '" & _
      Me.cboDogBreed & "' "
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Thank you again Duane!! It now works perfectly.
Sophia

 

[Dophia]

Hello Everyone:
This report from this post was working fine up until now. I now get "Run-time error '3270': Property not found.

I haven't changed anything, so why doesn't this report work any more? It stops on :


DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, , strWhere

Any help would be greatly appreciated.
Sophia

 

[dhookom]

Does the report open by itself? Does your code compile? Is there any code in the report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: The report is opened from a form from which the user chooses different criteria to include in the report. From the error message, as I described above, you can "debug" and this is the code. It stops on this line in the following code:
"DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, , strWhere"

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "

If Not IsNull(Me.cboStartDate) Then
strWhere = strWhere & " And [Arrival_Date]>=#" & _
Me.cboStartDate & "# "
End If
If Not IsNull(Me.cboEndDate) Then
strWhere = strWhere & " And [Arrival_Date]<=#" & _
Me.cboEndDate & "# "
End If

If Not IsNull(Me.cboAnimalType) Then
strWhere = strWhere & " And [Category_ID]='" & _
Me.cboAnimalType & "' "
End If
If Not IsNull(Me.cboCatBreed) Then
strWhere = strWhere & " And [Cat Breed]='" & _
Me.cboCatBreed & "' "
End If

If Not IsNull(Me.cboDogBreed) Then

strWhere = strWhere & " And ([Dog Breed Main] Like ""*" & _
Me.cboDogBreed & "*"") "

End If

If Not IsNull(Me.cboDogBreedMix) Then

strWhere = strWhere & " And ([Dog Breed Mix] Like ""*" & _
Me.cboDogBreedMix & "*"") "
End If

If Not IsNull(Me.cboAdmission) Then
strWhere = strWhere & " And [Animal_Admission_Category]='" & _
Me.cboAdmission & "' "
End If
DoCmd.OpenReport "RptAnimalInfo_Find", acPreview, , strWhere

End Sub