I have a form with a subform to allow users to run a series of reports (subquery in grid view). The main form has multiple list boxes. They are all multi-select. All of them are values that users can select to filter what they want to report for, and 1 is to select the fields that the query will select and group by.
My issue is the the users have told me they only want the max date of one of the sub records to pull up. They may not want this to show, but they don't want all the records to show, just the most recent. This is the code that runs the report based on the listbox choices selected:
Basically, there are records for cars. They are set as being with 1 dealer, and they can be at any location. The location can change and there is an effective date for when a car is moved to a new location. When the users are running reports based on different cars, they only want the record for the most recent location to show up, whether they are showing that date or not. Any ideas how I can add this to the main query?
thanks!
misscrf
It is never too late to become what you could have been ~ George Eliot
My issue is the the users have told me they only want the max date of one of the sub records to pull up. They may not want this to show, but they don't want all the records to show, just the most recent. This is the code that runs the report based on the listbox choices selected:
Code:
Private Sub cmdSummary_Click()
Dim Mysql As String
Dim strCriteria As String
Dim strFields As String
Dim strQuery As String
Dim lbo As ListBox
Dim itm
Dim i As Long
strQuery = "qryCarLocationReportFields"
Set lbo = Me.lstFields
If Me.lstFields.ListIndex = -1 Then
For i = 0 To Me.lstFields.ListCount - 1
Me.lstFields.Selected(i) = True
Next
For Each itm In lbo.ItemsSelected
strFields = strFields & "[" & lbo.ItemData(itm) & "], "
Next
strFields = Left(strFields, Len(strFields) - 2)
Else
For Each itm In lbo.ItemsSelected
strFields = strFields & "[" & lbo.ItemData(itm) & "], "
Next
strFields = Left(strFields, Len(strFields) - 2)
End If
strCriteria = "1=1 "
strCriteria = strCriteria & _
BuildIn(Me.LstCarStatus, "[Car Status]", "'")
strCriteria = strCriteria & _
BuildIn(Me.lstDealer, "Dealer", "'")
strCriteria = strCriteria & _
BuildIn(Me.lstLocation, "Location", "'")
Mysql = "SELECT " & strFields & " FROM qryCarLocationReport Where " & strCriteria & " Group By " & strFields
CurrentDb.QueryDefs(strQuery).SQL = Mysql
Me.frmSubStatusCarQry.SourceObject = "QUERY." & strQuery
Me.frmSubStatusCarQry.Visible = True
End Sub
Basically, there are records for cars. They are set as being with 1 dealer, and they can be at any location. The location can change and there is an effective date for when a car is moved to a new location. When the users are running reports based on different cars, they only want the record for the most recent location to show up, whether they are showing that date or not. Any ideas how I can add this to the main query?
thanks!
misscrf
It is never too late to become what you could have been ~ George Eliot