report criteria

[Carrie]

I was wondering if anyone can help.

I set up a search form so that the user can select five criterias and press preview and a report with the data based on the criteria appears- it all works fine but now I have another problem. As long as the user selects all criterias (of which I have five) things work fine with the report. My problem is when they may only choose 3 out of the five criterias. I then start having problems and nothing appears in my report.

Basically I've created a Preview button on my search form and on the On Click Event have put the following code;

Private Sub Command73_Click()
On Error GoTo Err_Command73_Click

Dim stDocName As String


DoCmd.OpenReport "Query1", acPreview, , "[MenuCategoryID] = '" & [Forms]![Searchform1]![MenuCategoryID] & "' And [TypeofCourseID] = '" & [Forms]![Searchform1]![TypeofCourseID] & "' And [CourseCodeID] ='" & [Forms]![Searchform1]![CourseCodeID] & "' And [BrandCodeID] = '" & [Forms]![Searchform1]![BrandCodeID] & "' And [HouseCodeID] = '" & [Forms]![Searchform1]![HouseCodeID] & "'"


Exit_Command73_Click:
Exit Sub

Err_Command73_Click:
MsgBox Err.Description
Resume Exit_Command73_Click

End Sub


It all works fine as long as all the criterias are used!



Any tips will be appreciated as am a bit stuck!!!

Thanks for any help in advance!!!
Carrie

 

[doss]

We have something similar in our databse.

On the form with the criteria, you may want to try setting the default value to

="*"

That's the wildcard which should include everything....

Because your query is set up a little differently than ours, I'm not 100% sure that will work without changing something in the code.

In our database we have the default = "*" on the form and in the query itself in criteria we have

Like [Forms]![frmMainForm]![cboControl]

Maybe someone has another idea????

 

[WayneRyan]

carrie,

My approach is:

DoCmd.OpenReport "Query1", acPreview, , _
  "([MenuCategoryID] LIKE '*" & [Forms]![Searchform1]![MenuCategoryID] & "*' OR " & _
  " IsNull([Forms]![Searchform1]![MenuCategoryID])) And " & _
...

Repeating for each of the criteria.

That way if the criteria matches, its OK, otherwise it doesn't
participate.

Wayne