VBA feed form values into a report query

Hi Experts,

I utilize Allen Browne's form filter code below which works great, however, I would like to apply the same concept to a report instead.  i.e. the form's filters feed a query that the report is based on, but the open event of the report collects the form filter values instead of directly referring to the form filters in the query.  

I decided to try this route as I was struggling with putting the date filters directly in the report query when there were null values or between two date values....i.e. the form code handles dates as follows, but I want the report to handle dates in the same way:

Both dates      = only dates between (both inclusive)
Start date only = all dates from this one onwards
End date only   = all dates up to (and including this one)

The code below gets stuck at the end (the part I tried to figure out) which is:
Me.RecordSource = "SELECT * FROM q_FILTER_0_Months_Main_Report WHERE strWhere"

The message that pops up is 'Enter Parameter value:  strWhere

Conceptually, I have no idea how to fix this.  

Thanks in advance for any help!





Private Sub Report_Open(Cancel As Integer)
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
   
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    'If Not IsNull(Me.txtFilterCity) Then
       ' strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
    'End If
   
    ''COMBO BOX TEXT field. (Add two extra quotes)
    If Not IsNull(Forms![0_Main].Combo_TXT_ASSIGNED_TO) Then
        strWhere = strWhere & "([AUTHORIZED_BY] = """ & Forms![0_Main].Combo_TXT_ASSIGNED_TO & """) AND "
    End If
   
    ''COMBO BOX TEXT field. (Add two extra quotes)
    If Not IsNull(Forms![0_Main].Combo_Completed) Then
        strWhere = strWhere & "([Completed] = """ & Forms![0_Main].Combo_Completed & """) AND "
    End If
   
    ''TEXT BOX with LIKE Field. (Use Like to find anywhere in the field.)
    If Not IsNull(Forms![0_Main].Txt_Borrower) Then
        strWhere = strWhere & "([ACCOUNT] Like ""*" & Forms![0_Main].Txt_Borrower & "*"") AND "
    End If
   
    'Date field example. From To example.
    If Not IsNull(Forms![0_Main].Txt_Entry_Date_From) Then
        strWhere = strWhere & "([Entry_Date] >= " & Format(Forms![0_Main].Txt_Entry_Date_From, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Forms![0_Main].Txt_Entry_Date_To) Then   'Less than the next day.
        strWhere = strWhere & "([Entry_Date] < " & Format(Forms![0_Main].Txt_Entry_Date_To + 1, conJetDate) & ") AND "
    End If
   
'Date field example. From To example.
    If Not IsNull(Forms![0_Main].Txt_Release_Date_From) Then
        strWhere = strWhere & "([Date_Released] >= " & Format(Forms![0_Main].Txt_Release_Date_From, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Forms![0_Main].Txt_Release_Date_To) Then   'Less than the next day.
        strWhere = strWhere & "([Date_Released] < " & Format(Forms![0_Main].Txt_Release_Date_To + 1, conJetDate) & ") AND "
    End If

   '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
       
        'Finally, apply the string as the form's Filter. <<Instead I would like to apply the string to the query that feeds the report>>>
        Me.RecordSource = "SELECT * FROM q_FILTER_0_Months_Main_Report WHERE strWhere"
    End If
End Sub
grmcraAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
change this

Me.RecordSource = "SELECT * FROM q_FILTER_0_Months_Main_Report WHERE strWhere"


with

Me.RecordSource = "SELECT * FROM q_FILTER_0_Months_Main_Report WHERE " & strWhere
0
 
grmcraAuthor Commented:
Thank you : )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.