Custom function as query filter/non-filter

[dsatino]

I have an Access query whose results are filtered by a custom VBA function. This works perfectly as long as the function returns a result. If the function doesn't return a result, neither does the query. This makes sense, but I'm trying to figure out a way to for the query to return all results when the function returns nothing.

The VBA function result is based on a form control option box where two options cause the query to filter and the third should produce no filter. I know that I can rewrite the querydef to modify the WHERE clause, but it just seems like filter-by-function method is cleaner. Does anyone have any ideas of how I can get the custom function to act as both a filter and a non-filter?

[TheSmileyCoder]

You could insert an IIF statement like this in your querydef.

Iif(CustomFunction() & ""<>"";CustomFunction();)

If the CustomFunction( ) returns something, the first statement will be used, if it returns nothing, it will use the second statement (Which is blank). Im not 100% sure if it will work with the blank statement, if it does't insert this ["Like '*'"] into the second statement (After the second ;), without the square brackets.

If you need more help, please post your VBA code. Remember to use the Code tags.

[dsatino]

Thanks for the quick reply, but it doesn't work. The blank seems to translate to NULL, "Like '*'" translates to text, and the 'Like' statement without quotes returns nothing. I think the main problem is that the IIF statement is evaluated after the SQL statement is constructed

[TheSmileyCoder]

Please post your current SQL statement and your VBA function.

[dsatino]

Please post your current SQL statement and your VBA function.

SQL:

SELECT Year_chartbase.FlxID, Sum(Year_chartbase.Transactions) AS Transactions, Sum(Year_chartbase.[New $]) AS [New $], Trim([Last Name]) & "- " & Trim([First Name]) AS Agent, EmployeeGroupType.Group, [new $]/[transactions] AS RevPerTrans, aa_Region.Region, aa_Entity.District, aa_Entity.BranchName, Left([aa_region.region],1) & [district] AS DstName
FROM ((EmployeeType INNER JOIN (Employee INNER JOIN Year_chartbase ON Employee.Flxid = Year_chartbase.FlxID) ON EmployeeType.[Agent Type] = Employee.Title) INNER JOIN EmployeeGroupType ON EmployeeType.Group = EmployeeGroupType.Group) INNER JOIN ((aa_Entity INNER JOIN aa_Region ON aa_Entity.Region = aa_Region.RegionKey) INNER JOIN aa_BC_tbl ON aa_Entity.BranchCode = aa_BC_tbl.BranchCode) ON Employee.BC = aa_BC_tbl.[Budget Center]
WHERE (((Employee.Status)="Active") AND ((Year_chartbase.Date) Between userYrStart() And UserEnd()) AND ((EmployeeGroupType.Key)=AgtTypeSlct()))
GROUP BY Year_chartbase.FlxID, Trim([Last Name]) & "- " & Trim([First Name]), EmployeeGroupType.Group, aa_Region.Region, aa_Entity.District, aa_Entity.BranchName, Left([aa_region.region],1) & [district]
HAVING (((Sum(Year_chartbase.Transactions)) Is Not Null) AND ((Sum(Year_chartbase.[New $]))>0))
ORDER BY Year_chartbase.FlxID

VBA:

Public Function AgtTypeSlct()
    If Form_Dash.Frame155 <> 3 Then AgtTypeSlct = Form_Dash.Frame155
End Function

[MMcCarthy]

If you change your function to return a string as follows:

Public Function AgtTypeSlct() As String
    If Form_Dash.Frame155 <> 3 Then 
        AgtTypeSlct = """ & Form_Dash.Frame155 & """
    Else
        AgtTypeSlct = "#" 
    End if

End Function

Now change the WHERE statement in your query to ...

AND ((EmployeeGroupType.Key) LIKE AgtTypeSlct()))

This should work. Essentially

EmployeeGroupType.Key Like "1"

will return all records where key = 1

and

EmployeeGroupType.Key Like "#"

will return ALL records