By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,714 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,714 IT Pros & Developers. It's quick & easy.

Custom function as query filter/non-filter

dsatino
100+
P: 393
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?
May 3 '10 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
You could insert an IIF statement like this in your querydef.
Expand|Select|Wrap|Line Numbers
  1. 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.
May 3 '10 #2

dsatino
100+
P: 393
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
May 3 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Please post your current SQL statement and your VBA function.
May 3 '10 #4

dsatino
100+
P: 393
@TheSmileyOne
SQL:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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]
  3. WHERE (((Employee.Status)="Active") AND ((Year_chartbase.Date) Between userYrStart() And UserEnd()) AND ((EmployeeGroupType.Key)=AgtTypeSlct()))
  4. 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]
  5. HAVING (((Sum(Year_chartbase.Transactions)) Is Not Null) AND ((Sum(Year_chartbase.[New $]))>0))
  6. ORDER BY Year_chartbase.FlxID

VBA:

Expand|Select|Wrap|Line Numbers
  1. Public Function AgtTypeSlct()
  2.     If Form_Dash.Frame155 <> 3 Then AgtTypeSlct = Form_Dash.Frame155
  3. End Function
May 3 '10 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
If you change your function to return a string as follows:

Expand|Select|Wrap|Line Numbers
  1. Public Function AgtTypeSlct() As String
  2.     If Form_Dash.Frame155 <> 3 Then 
  3.         AgtTypeSlct = """ & Form_Dash.Frame155 & """
  4.     Else
  5.         AgtTypeSlct = "#" 
  6.     End if
  7.  
  8. End Function
Now change the WHERE statement in your query to ...

Expand|Select|Wrap|Line Numbers
  1. AND ((EmployeeGroupType.Key) LIKE AgtTypeSlct()))
This should work. Essentially

Expand|Select|Wrap|Line Numbers
  1. EmployeeGroupType.Key Like "1"
will return all records where key = 1

and

Expand|Select|Wrap|Line Numbers
  1. EmployeeGroupType.Key Like "#"
will return ALL records
Jun 12 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.