Dynamic Filter Only Showing One Record

[alsoascientist]

I have a VBA code that filters a subform dependent on the value of the current control. This has been working fine until I "tidied up" my VBA (I don't think I deleted anything, just moved things about in the same modules).

The filter should show all records that match the criteria from a query, but only one record is being returned (this used to work perfectly).

The code I have is

Me.PivotQuerySubform.Form.RecordSource = "SELECT * FROM [AllQuery] " & BuildFilter
Me.PivotQuerySubform.Requery
(Attached to the after update function of each control)

'-------------------------------------------------
'This will build the filter to call from the query
'-------------------------------------------------
Private Function BuildFilter() As Variant
Dim varWhere As Variant

select1 = Forms![FrmAdd]![CurrentControl].Value
select2 = Forms![FrmAdd]![CurrentValue].Value
varWhere = Null ' Main filter
' Check for Search 1
If select1 > "" Then
varWhere = varWhere & "[" & select1 & "] ='" & select2 & "'"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
End If
BuildFilter = varWhere
End Function

I really can't see what I have done - I have a very similar one on a seperate form that I use as a search, and that one works fine (I have tried comparing them but I can't see what is different apart from what actually should be different)

 

[Beetle]

Well, this line looks wrong (assuming it's not just a typo in your post);

If select1 > "" Then

When comparing two strings (especially if one of them is zero length) it would normally be equal (=) or not equal (<>), not greater than. However, I would think that in this case (i.e. the comparison always evaluates to False) your query would be returning all records (because it ends up with no Where clause), not one record, so there may be something else going on but nothing else necessarily stands out from your post.

BTW - When posting example code, it makes it easier to read if you wrap it in code tags (the hash # tool button).

 

[alsoascientist]

Thanks Beetle,

I tried with both but neither is helping. I have a feeling that it may be to do with the relationships between the tables as this was working when it was looking up a seperate query for each return and it isn't now. I might try a SELECT DISTINCT ROW instead (though I never like to be beaten by things like this!)

(and I'll remember the # next time!)

 

[alsoascientist]

In case anyone else was browsing this and looking for a resolution, I found that I had

Dim select1 as String
Dim select2 as String

... changing this to Variant has worked a charm!