boblarson
Smeghead
- Local time
- Today, 02:17
- Joined
- Jan 12, 2001
- Messages
- 32,059
Sorry for the long post, but I had to do it.
I have been working for a long time on this criteria form for a report. The filter code for anything which is on the main report has been easy to implement. The tricky part is that there are fields that need to be able to be used as criteria which exist on any of two subreports as well.
I know how to be able to filter the report based on the items in the subreports (we have a propertyID field which is applicable to both the main report and the subreports, so if I can get the property ID's which match the criteria on the subreports, I will be able to limit at the main report by those property id's which match.
The tricky part comes in that I have 4 distinct subreports which appear on the report. So, what I've done to this point is:
1. If the item is from a subreport, I have a SQL string which I build to pull only those propertyID's which match the criteria for that item and I put it into an array.
2. For each of them I have an array which is built.
3. The hard part I am having deciding on how to do is that I need to check each of the arrays against the others to only pull out the propertyID's which all match, EXCEPT that if one, or more, of the items based on the subreports is NOT used, then don't include it in the matching code as there would not be an array for them anyway and there wouldn't be any propertyID's in there.
4. If I just combine the arrays, that would give me sort of what I want but it would only ADD properties to the main query because it would not limit to only those that matched based on the criteria.
5. Here's the code I started off using which works to get the ID's which match in all three (will need to expand to the fourth as well) arrays. The problem is, that if no criteria is entered for, say TCPrice then none of the other two arrays will have any properties which match and the result is none of them, where there clearly should be those which match between the two that have criteria. I need to figure out a way to only look at the ones which have been used and have created their array, and that is my main stumbling block.
And just a pic to show what the form looks like (still under construction and I have thought about some formatting changes, and some other changes):
I have been working for a long time on this criteria form for a report. The filter code for anything which is on the main report has been easy to implement. The tricky part is that there are fields that need to be able to be used as criteria which exist on any of two subreports as well.
I know how to be able to filter the report based on the items in the subreports (we have a propertyID field which is applicable to both the main report and the subreports, so if I can get the property ID's which match the criteria on the subreports, I will be able to limit at the main report by those property id's which match.
The tricky part comes in that I have 4 distinct subreports which appear on the report. So, what I've done to this point is:
1. If the item is from a subreport, I have a SQL string which I build to pull only those propertyID's which match the criteria for that item and I put it into an array.
2. For each of them I have an array which is built.
3. The hard part I am having deciding on how to do is that I need to check each of the arrays against the others to only pull out the propertyID's which all match, EXCEPT that if one, or more, of the items based on the subreports is NOT used, then don't include it in the matching code as there would not be an array for them anyway and there wouldn't be any propertyID's in there.
4. If I just combine the arrays, that would give me sort of what I want but it would only ADD properties to the main query because it would not limit to only those that matched based on the criteria.
5. Here's the code I started off using which works to get the ID's which match in all three (will need to expand to the fourth as well) arrays. The problem is, that if no criteria is entered for, say TCPrice then none of the other two arrays will have any properties which match and the result is none of them, where there clearly should be those which match between the two that have criteria. I need to figure out a way to only look at the ones which have been used and have created their array, and that is my main stumbling block.
Code:
Function GetUniquePIDs() As String
Dim lngCountEquity As Long
Dim lngCountTCPrice As Long
Dim lngCountTCAmount As Long
Dim strHold As String
Dim blnFound As Boolean
' The equity array
If IncludeEquity Then
For lngCountEquity = 0 To UBound(garrEquity)
If IncludeTCPrice Then
For lngCountTCPrice = 0 To UBound(garrTCPrice)
If garrEquity(lngCountEquity) = garrTCPrice(lngCountTCPrice) Then
blnFound = True
End If
Next
End If
If IncludeTCAmount Then
For lngCountTCAmount = 0 To UBound(garrTCAmount)
If garrEquity(lngCountEquity) = garrTCAmount(lngCountTCAmount) Then
blnFound = True
End If
Next
End If
If blnFound Then
If InStr(1, strHold, garrEquity(lngCountEquity), vbTextCompare) = 0 Then
strHold = strHold & garrEquity(lngCountEquity) & ","
End If
End If
blnFound = False
Next
End If
' The TC Price array
If IncludeTCPrice Then
For lngCountTCPrice = 0 To UBound(garrTCPrice)
If IncludeEquity Then
For lngCountEquity = 0 To UBound(garrEquity)
If garrTCPrice(lngCountTCPrice) = garrEquity(lngCountEquity) Then
blnFound = True
End If
Next
End If
If IncludeTCAmount Then
For lngCountTCAmount = 0 To UBound(garrTCAmount)
If garrTCPrice(lngCountTCPrice) = garrTCAmount(lngCountTCAmount) Then
blnFound = True
End If
Next
End If
If blnFound Then
If InStr(1, strHold, garrTCPrice(lngCountTCPrice), vbTextCompare) = 0 Then
strHold = strHold & garrTCPrice(lngCountTCPrice) & ","
End If
End If
blnFound = False
Next
End If
' The TC Amount Array
If IncludeTCAmount Then
For lngCountTCAmount = 0 To UBound(garrTCAmount)
If IncludeEquity Then
For lngCountEquity = 0 To UBound(garrEquity)
If garrTCAmount(lngCountTCAmount) = garrEquity(lngCountEquity) Then
blnFound = True
End If
Next
End If
If IncludeTCPrice Then
For lngCountTCPrice = 0 To UBound(garrTCPrice)
If garrTCAmount(lngCountTCAmount) = garrTCPrice(lngCountTCPrice) Then
blnFound = True
End If
Next
End If
If blnFound Then
If InStr(1, strHold, garrTCAmount(lngCountTCAmount), vbTextCompare) = 0 Then
strHold = strHold & garrTCAmount(lngCountTCAmount) & ","
End If
End If
If Right(strHold, 1) = "," Then
strHold = Left(strHold, Len(strHold) - 1)
End If
blnFound = False
Next
End If
Debug.Print strHold
GetUniquePIDs = strHold
End Function
And just a pic to show what the form looks like (still under construction and I have thought about some formatting changes, and some other changes):