Sacha Walter
asked on
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_Rep ort 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].Comb o_TXT_ASSI GNED_TO) Then
strWhere = strWhere & "([AUTHORIZED_BY] = """ & Forms![0_Main].Combo_TXT_A SSIGNED_TO & """) AND "
End If
''COMBO BOX TEXT field. (Add two extra quotes)
If Not IsNull(Forms![0_Main].Comb o_Complete d) Then
strWhere = strWhere & "([Completed] = """ & Forms![0_Main].Combo_Compl eted & """) 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_Borrowe r & "*"") 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_Da te_From) Then
strWhere = strWhere & "([Date_Released] >= " & Format(Forms![0_Main].Txt_ Release_Da te_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_Da te_To) Then 'Less than the next day.
strWhere = strWhere & "([Date_Released] < " & Format(Forms![0_Main].Txt_ Release_Da te_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_Rep ort WHERE strWhere"
End If
End Sub
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_Rep
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].Comb
strWhere = strWhere & "([AUTHORIZED_BY] = """ & Forms![0_Main].Combo_TXT_A
End If
''COMBO BOX TEXT field. (Add two extra quotes)
If Not IsNull(Forms![0_Main].Comb
strWhere = strWhere & "([Completed] = """ & Forms![0_Main].Combo_Compl
End If
''TEXT BOX with LIKE Field. (Use Like to find anywhere in the field.)
If Not IsNull(Forms![0_Main].Txt_
strWhere = strWhere & "([ACCOUNT] Like ""*" & Forms![0_Main].Txt_Borrowe
End If
'Date field example. From To example.
If Not IsNull(Forms![0_Main].Txt_
strWhere = strWhere & "([Entry_Date] >= " & Format(Forms![0_Main].Txt_
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_
strWhere = strWhere & "([Entry_Date] < " & Format(Forms![0_Main].Txt_
End If
'Date field example. From To example.
If Not IsNull(Forms![0_Main].Txt_
strWhere = strWhere & "([Date_Released] >= " & Format(Forms![0_Main].Txt_
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_
strWhere = strWhere & "([Date_Released] < " & Format(Forms![0_Main].Txt_
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_Rep
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER