Combo box with subform issue

[Trevor G]

I have a search form which has a number of combo boxes which will allow me to filter. There is an issue with the filtered function.

If I select one of the combos and select (BUG Field Name) and filter from the combo I get the following results

CAR = 452 (In the table there are 500)
International = 15535 (In the table there are 16139)
Professional Indemnity = 33828 (In the table there are 34243)
UK and Ireland = 56140 (In the table there are 56739)
The combo Record source is the same as the table, but grouped together and the SQL view is as follows:

SELECT Combined.BUG
FROM Combined
GROUP BY Combined.BUG;

The code that is behind the Command Button is as follows:

Dim strUWYear As String
Dim strBUG As String
Dim strTerritory As String
Dim strPortFolio As String
Dim strPolicyRef As String
Dim strInsuredName As String
Dim strTrade As String
Dim strUnderWriter As String

If IsNull(Me.cboUWYear.Value) Then
strUWYear = "Like '*'"
Else
strUWYear = "='" & Me.cboUWYear.Value & "'"
End If
If IsNull(Me.cboBUG.Value) Then
strBUG = "Like '*'"
Else
strBUG = "='" & Me.cboBUG.Value & "'"
End If
If IsNull(Me.cboTerritoryName.Value) Then
strTerritory = "Like '*'"
Else
strTerritory = "='" & Me.cboTerritoryName.Value & "'"
End If
If IsNull(Me.cboPortFolio.Value) Then
strPortFolio = "Like '*'"
Else
strPortFolio = "='" & Me.cboPortFolio.Value & "'"
End If
If IsNull(Me.cboPolicyNumber.Value) Then
strPolicyRef = "Like '*'"
Else
strPolicyRef = "='" & Me.cboPolicyNumber.Value & "'"
End If
If IsNull(Me.cboInsuredName.Value) Then
strInsuredName = "Like '*'"
Else
strInsuredName = "='" & Me.cboInsuredName.Value & "'"
End If
If IsNull(Me.cboTrade.Value) Then
strTrade = "Like '*'"
Else
strTrade = "='" & Me.cboTrade.Value & "'"
End If
If IsNull(Me.cboUnderWriter.Value) Then
strUnderWriter = "Like '*'"
Else
strUnderWriter = "='" & Me.cboUnderWriter.Value & "'"
End If
Me.frmCombinedSearchEngineFilter.Form.Filter = "[UWYear]" & strUWYear & " AND [BUG]" & strBUG & " AND [Location1]" & strTerritory & " AND [Reporting Class 2]" & strPortFolio _
& " AND [Policy Ref]" & strPolicyRef & " AND [INSURED NAME]" & strInsuredName & " AND [Trade]" & strTrade & " AND [UWR]" & strUnderWriter
Me.frmCombinedSearchEngineFilter.Form.FilterOn = True

 

[JANR]

GroupBy eliminates duplicates, so do you have duplicates in your table? It would explaine the diffrences in the results.

JR

 

[Trevor G]

GroupBy eliminates duplicates, so do you have duplicates in your table? It would explaine the diffrences in the results.

JR

No there are no duplicates, I took of the GroupBy and still exactly the same issue.

Thank you for the suggestion though.

 

[Malashaan]

You could try replacing the "like *" in the IsNull clauses with " Like '%'". * isn't a wildcard in SQL like it is in Microsoft OS and it looks from your code like you want a wildcard, which in this case would be the % character.

 

[Trevor G]

You could try replacing the "like *" in the IsNull clauses with " Like '%'". * isn't a wildcard in SQL like it is in Microsoft OS and it looks from your code like you want a wildcard, which in this case would be the % character.

Sorry to say the % sign didn't work.

I have gone back to the design of the query and altered it slightly to use

Like [Form]![My Form Name]![My Combo Box]&"*" Or Is Null

I have done that for each of the combo box and fields.

I also used SQL Update queries to replace anything that is empty with "A Missing" and this seems to have worked well