Query not combining and filtering values based on a form
- Thread starter mayestom
- Start date
The button you created to export the filtered results to a report, is that created by the standard create a 'macro' button on the ribbon? Maybe not, I just looked at the code and you added code to the VBA to do that? The reason I ask is bc I would like to do the same to the other query.
So I would create a command button on the other query and on the On Click I would add this code and modify it to the other query?
Code:
Private Sub Command34_Click()
'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.cboLocation) Then
strWhere = strWhere & "([Location/Affiliate_Name] = """ & Me.cboLocation & """) AND "
End If
If Not IsNull(Me.cboProject) Then
strWhere = strWhere & "([Project_Name] Like ""*" & Me.cboProject & "*"") AND "
End If
If Not IsNull(Me.cboSupplierID) Then
strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplierID.Column(1) & """) 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.
DoCmd.OpenReport "rptMultiSelectqry", acViewPreview
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.
DoCmd.OpenReport "rptMultiSelectqry", acViewPreview, , strWhere
DoCmd.Maximize
Reports![rptMultiSelectqry].Filter = strWhere
Reports![rptMultiSelectqry].FilterOn = True
End If
End SubI think I did it wrong. This is the code that I ended up with after modifying it. It's opening up a report by it doesn't seem right.
Code:
Private Sub cmdOpenReport_Click()
'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.cboEquipment) Then
strWhere = strWhere & "([Madeup Code] = """ & Me.cboEquipment & """) AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboSupplier.Column(1)) Then
strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier.Column(1) & """) AND "
End If
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboYear) Then
strWhere = strWhere & "(Year([Purchase_Date]) = """ & Me.cboYear & """) AND "
End If
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboMonthYear) Then
strWhere = strWhere & "(Format([Purchase_Date],'m-yyyy') = """ & Me.cboMonthYear & """) AND "
End If
'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtFromDate) Then
strWhere = strWhere & "([Purchase_Date] >= " & Format(Me.txtFromDate, 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(Me.txtToDate) Then 'Less than the next day.
strWhere = strWhere & "([Purchase_Date] <= " & Format(Me.txtToDate, 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.
DoCmd.OpenReport "rptMultiSelectqry", acViewPreview
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.
DoCmd.OpenReport "rptMultiSelectqry", acViewPreview, , strWhere
DoCmd.Maximize
Reports![rptMultiSelectqry].Filter = strWhere
Reports![rptMultiSelectqry].FilterOn = True
End If
End SubWait, I think I just did it! I created a report of off an unfiltered query and named it rptSupplierDescriptionCodeqry. Then I modified the bottom of the code to this:
Code:
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
DoCmd.OpenReport "rptSupplierDescriptionCodeqry", acViewPreview
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.
DoCmd.OpenReport "rptSupplierDescriptionCodeqry", acViewPreview, , strWhere
DoCmd.Maximize
Reports![rptSupplierDescriptionCodeqry].Filter = strWhere
Reports![rptSupplierDescriptionCodeqry].FilterOn = True
End If
Yes it's working! Hi Gina, I'm sorry to bother you again but if I wanted to sort the month-year combo box, how could I do that? I have tried selecting “Ascending” through the query but that didn’t work. Presently the sql for it is:
Code:
SELECT Format([Purchase_Date],"m-yyyy") AS MyMonthYear
FROM Equipment
GROUP BY Format([Purchase_Date],"m-yyyy")
HAVING (((Format([Purchase_Date],"m-yyyy"))<>" "))
ORDER BY Format([Purchase_Date],"m-yyyy");
GinaWhipp
AWF VIP
- Local time
- Today, 17:47
- Joined
- Jun 21, 2011
- Messages
- 5,899
Okay, me see if I can explain this clearly...
You need to add two fields to the query both using Purchase_Date and they are...
Year([Purchase_Date])
and
Month([Purchase_Date])
...in that order. Then select to sort ASC and finally, uncheck the box for them to show so that they are hidden. Now test, your query should be sorting ascending.
You need to add two fields to the query both using Purchase_Date and they are...
Year([Purchase_Date])
and
Month([Purchase_Date])
...in that order. Then select to sort ASC and finally, uncheck the box for them to show so that they are hidden. Now test, your query should be sorting ascending.
