Compond criteria with DoCmd.OpenReport

[tamus121]

Hi first time posting. I have been struggling with this in various forms for days. When previewing a report this works :-
[DoCmd.OpenReport stDocName, acPreview, , "Inspection_Type = Forms!InsReports.Reporttype1"]

but I want to base the report on various criteria depending on users options selection. I am using If.. Then.. Else If .. Then.. Else.. End If to select the report needed. When I join two criteria with And I get "Type Mismatch" as below :-

[DoCmd.OpenReport stDocName, acPreview, , "InspectionType = Forms!InsReports.Reporttype" And "Inspector = Forms!InsReports.InspCode1"]

Any ideas as to what I am doing wrong please
tamus

 

[dhookom]

It would help if you provided your data types. I always create a strWhere variable to build prior to the DoCmd.OpenReport method. Assuming InspectionType is numeric and Inspector is text, try:

Dim strWhere as String
strWhere = "InspectionType = " & Forms!InsReports.Reporttype & _
   " And Inspector = """ & Forms!InsReports.InspCode1 & """"

DoCmd.OpenReport stDocName, acPreview, , strWhere

If the code is running in Forms!InsReports then simply use:

Dim strWhere as String
strWhere = "InspectionType = " & Me.Reporttype & _
   " And Inspector = """ & Me.InspCode1 & """"

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]

 

[tamus121]

Both are Text but I have amended your suggestion to suit and as the code is in the Forms!InsReports I have taken on your second method. thanks very much, it works well, I have 3 frames with options and now have a possible total of 240 different reports to view.
Tried to put my own code in display box as you have done but I have missed something, (a quick pointer please)
tamus

 

[dhookom]

You can use TGML to display colors, font styles, and other stuff. Click the Preview Post button and find a link on TGML help.

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]