Multiple criteria report

[Carrie]

Hi

I have a form that I'm using as a search/filter form. The user has five combo boxes that they can use to choose their criteria and then I want them to beable to click a button and preview a report based on the criteria in the form .

Have got to creating the code for the Preview button but have now got a bit stuck as my fields my criterias are based on are all text fields! I know I have to put commas and stuff in as they are text fields but so far the attempts I have made have not worked. I'm now getting myself in a bit of a muddle!

Can anyone show me what to do. Here's the code I need to sort out.

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

Any help will be much appreciated!

On a final note will this code still work if only 2 of the five criterias are selected??




Thank you!
Carrie

 

[Paul_Bricker]

Assuming all fields are Text datatype try this
"[MenuCategoryID] = '" & [Forms]![Searchform1]![MenuCategoryID] & "' And [TypeofCourseID] = '" & [Forms]![Searchform1]![TypeofCourseID] & "' And [CourseCodeID] = '" &[Forms]![Searchform1]![CourseCodeID] & "' And [BrandCodeID] = '" & [Forms]![Searchform1]![BrandCodeID] & "' And [HouseCodeID] '" & = [Forms]![Searchform1]![HouseCodeID] & "'"

the form values are surrounded by singlequote/doublequote & FormValue & doublequote/singlequote

' " & Forms!FormName!FieldName & " '

I put this here so you can actully see what the syntax has to be and what order it has to be in for Text datatypes. If the Fields are numeric datatype, then you surround them with just " & ... & "
quote marks.

Paul

 

[Carrie]

Thank you!

That has helped me greatly!