473,750 Members | 2,209 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Passing report results as the argument in a custom/user-defined function

I'd like to use the Excel.Worksheet Function 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.

Example:
The report's Record Source is a query that fetches salaries from a
database, "SELECT JobID, JobTitle, BaseSalary ORDER BY JobTitle".

The report groups BaseSalary by JobTitle.

I have a textbox with Control Source of "=Avg([BaseSalary])"
I have another textbox with Control Source of "=Perc25([BaseSalary])",
where Perc25 is the custom function that calls
Excel.Worksheet Function.Percen tile

The first textbox, which uses the built-in function "Avg", correctly
computes Average using all of the BaseSalary values for that
particular JobTitle.

The second textbox only displays the value of Perc25 for the last
listed BaseSalary. Is there a way to reference the complete set of
BaseSalary values for that particular JobTitle?

Thank you for your help!
Nov 12 '05 #1
1 2197
Just a wild stab in the dark but maybe this isn't working because Avg is a
built in access aggregate function and your custom function isn't.

I'm guessing that if you were to sum the values then perc them it would
work:

=Sum(Perc25([BaseSalary]))

Of course I could have totally misunderstood the problem...

"CapeCoder" <ca*******@cape coder.com> wrote in message
news:b9******** *************** ***@posting.goo gle.com...
I'd like to use the Excel.Worksheet Function 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.

Example:
The report's Record Source is a query that fetches salaries from a
database, "SELECT JobID, JobTitle, BaseSalary ORDER BY JobTitle".

The report groups BaseSalary by JobTitle.

I have a textbox with Control Source of "=Avg([BaseSalary])"
I have another textbox with Control Source of "=Perc25([BaseSalary])",
where Perc25 is the custom function that calls
Excel.Worksheet Function.Percen tile

The first textbox, which uses the built-in function "Avg", correctly
computes Average using all of the BaseSalary values for that
particular JobTitle.

The second textbox only displays the value of Perc25 for the last
listed BaseSalary. Is there a way to reference the complete set of
BaseSalary values for that particular JobTitle?

Thank you for your help!

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
3968
by: Alex Vinokur | last post by:
Various forms of argument passing ================================= C/C++ Performance Tests ======================= Using C/C++ Program Perfometer http://sourceforge.net/projects/cpp-perfometer http://alexvn.freeservers.com/s1/perfometer.html
3
2902
by: RC | last post by:
I can't quite grasp the concept of creating custom reports depending upon what options a user picks on a Form. For example, the user clicks on a "Print Reports" button and a Form pops up. On the Form the user can choose to get a report that shows the Box Numbers that are at Warehouse A, Warehouse B or Warehouse C. and then click on the Print Preview button and get a report showing only the Box Numbers in whichever warehouse was...
2
1509
by: Pasquale | last post by:
How can I get a combo box to come up when previewing a report so that the user can select an event name and then print preview the report specific to that event? In the "On Open" event of the report I've tried 'Forms!!' and in the text box of the report I have the "control source" as '=Forms!Events2!EventName.Value'. Using these results in '#Error' being displayed in where the text box is. Any help or direction would be appreciated.
1
1901
by: Chasing Gates | last post by:
I have created a database that brings in a new table weekly. I then made a separate query for each sales rep and a separate report for each rep. (The reports are all identical but call different queries.) Lastly, I created a macro that sends the reports to the respective rep via e-mail with the push of a button. I am pleased with myself for getting this far but I realize the next step is figure out how to use one report (so I don't...
3
4520
by: Lyn | last post by:
Hi, I have a Search input form which collects from the user a person's name. I am using LIKE with a "%" suffix in the SQL so that the user does not have to type in the full name. When they hit the Search button, a query is run to search the Person table for a match. This produces a recordset (I am using ADO). If the RecordCount is zero, they get a No Match message. If the RecordCount is 1, a DoCmd.OpenForm is performed to open the...
11
3184
by: Arsen Vladimirskiy | last post by:
Hello, If I have a few simple classes to represent Entities such as Customers and Orders. What is the proper way to pass information to the Data Access Layer? 1) Pass the actual ENTITY to the Data Access Layer method -or- 2) Pass some kind of a unique id to the Data Access Layer method
6
3255
by: Scott Zabolotzky | last post by:
I'm trying to pass a custom object back and forth between forms. This custom object is pulled into the app using an external reference to an assembly DLL that was given to me by a co-worker. A query-string flag is used to indicate to the page whether it should instantiate a new instance of the object or access an existing instance from the calling page. On the both pages I have a property of the page which is an instance of this custom...
11
1967
by: Mike | last post by:
I have done this many times but for some reason it's not working, the raise postback event fires but the eventArgument passed to it is empty. This is a simple custom control that implements IPostBackEventHandler and I get the href for the a tag using string href = this.Page.GetPostBackClientHyperlink(this, tab.TabID.ToString());
20
2170
by: jason | last post by:
Hello, I'm a beginning C programmer and I have a question regarding arrays and finding the number of entries present within an array. If I pass an array of structures to a function, then suddenly I can't use sizeof(array) / sizeof(array) anymore within that function ? Help - What point am I missing ?
4
13358
by: RachH | last post by:
Is there a way to pass a value to a report via VBA so the value can then be displayed in the report as a concantenation with a string? I have a report that displays all pricings that have occured in one day. I would like to display the pricing date in the report header. However, if there were no pricings for the date being viewed, I return no records so I can't pull the pricing date from my query results and thus want to set it...
0
9394
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9256
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8260
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6803
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6080
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4712
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4885
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3322
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.