473,322 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Custom function as query filter/non-filter

dsatino
393 256MB
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
5 2951
TheSmileyCoder
2,322 Expert Mod 2GB
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
393 256MB
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
2,322 Expert Mod 2GB
Please post your current SQL statement and your VBA function.
May 3 '10 #4
dsatino
393 256MB
@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
14,534 Expert Mod 8TB
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

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

Similar topics

9
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my...
0
by: Sundown | last post by:
I am trying to create a custom button control for the web that, when clicked, disables and changes the text of itself and a bunch of other controls (in the collection). My goal is to end up with a...
5
by: Jason Butera | last post by:
I know that I can read/write custom properties of an object by using the following: Setting: document.all.customProp = "this"; Getting: document.all.customProp; Is there a way I can run...
1
by: CapeCoder | last post by:
I'd like to use the Excel.WorksheetFunction library to compute median and percentiles in a user-defined function. I'd like to use the data from the calling report as the function argument. ...
22
by: TC | last post by:
I have an Access database application with a lot of custom row functions written in VBA. In other words, a lot of queries contain calculated fields which use functions defined in the modules. I...
1
by: DeveloperNC | last post by:
I've setup a custom 404 error page for my asp.net site. I have configured IIS to process non aspx pages and also added the correct web.config custom error section to the applicaiton. Every thing is...
0
by: bluedog357 | last post by:
Hello all, I've been at trying to implement custom session handlers with a postgreSQL database and am having some trouble with the garbage collection. What I've got now sure doesn't work: ...
14
by: Rolf Welskes | last post by:
Hello, I have an ObjectDataSource which has as business-object a simple array of strings. No problem. I have an own (custom) control to which I give the DataSourceId and in the custom-control...
5
by: slynn | last post by:
I am trying to build a query which will uses a custom function as criteria. I have searched the forums and found nothing, so either it can't be done or I am just missing something simple. The...
8
Cintury
by: Cintury | last post by:
The problem is I have a function that I've created and stored in a module. I call it as an expression (e.g. total: Function(parameter)). I'm receiving the error 3061: too few parameters, expected 1....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.