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?
5 2951
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.
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
Please post your current SQL statement and your VBA function.
@TheSmileyOne
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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:
...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |