Trying to Open Report Based off of Text Search Criteria

[SunnyDayInside]

Hi!

I have a pop up form that allows two text string searches. The code is:
Private Sub cmdSearch_Click()
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
GcriteriaSec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
If Not (Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True) Then
If Len(cboSearchField1) = 0 Or IsNull(cboSearchField1) = True Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
Form_frmServiceForm.RecordSource = "select * from [Service Table] where " & GCriteria
DoCmd.Close acForm, "frmSearch"
MsgBox "Results have been filtered."
Else
If Not (Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True) Then
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
GcriteriaSec = cboSearchField1.Value & " LIKE '*" & txtSearchString1 & "*'"
Form_frmServiceForm.RecordSource = "select * from [Service Table] where " & _
GCriteria & " And " & GcriteriaSec
DoCmd.Close acForm, "frmSearch"
MsgBox "Results have been filtered."
End If
End If
End If


End Sub

This shows records on the main form that contain the text searched for. Then I have a button on the main form that opens a report.
Private Sub cmdReport_Click()
DoCmd.OpenReport "Daily Service report", acViewPreview, , GCriteria
DoCmd.OpenReport "Daily Service report", acViewPreview, , GcriteriaSec
End Sub

The problem I am having is that only the GCriteria and NOT GCriteiasec is being applied to the report. I need a solution where a user can apply none, one, or both of the criteria to the report. I'm being trying to do this for days now, any help is appreciated!!

 

[pbaldy]

Have you tried

GCriteria & " And " & GcriteriaSec

In the OpenReport line? The second OpenReport won't do anything as the report is already open.

Paul
MS Access MVP 2007/2008

 

[SunnyDayInside]

I've been trying to get this to work for a few days and have posted in different forums and no one has been able to help me. But this actually works! And I swear I tried this code once already...but this time it works.

Thanks so much!

 

[pbaldy]

No problem; it seemed like the obvious solution, since it worked on the form. I sympathize with the "I thought I tried that". Just today I put something new in and the users said a particular thing didn't work. I could swear I'd tested it, but indeed it didn't work. I think the computer gods just do it to screw with us.


Paul
MS Access MVP 2007/2008