We help IT Professionals succeed at work.

Calculate in a report

on
428 Views
I am trying to calculate in a report. The following is what I have;

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Const lbPound As Integer = 16
Const galGallon As Integer = 128

If MesUnit = "Pound" Then
TtOrder = (MaxInvty - InvtyOnHand) / lbPound
ElseIf MesUnit = "Gallon" Then
TtOrder = (MaxInvty - InvtyOnHand) / galGallon
End If
End Sub

What I want to do is calculate for each record. As I have right now it is calculating only the fist record and placing to all record in my report.

Any help is much appreciate.

blee
Comment
Watch Question

View Solution Only

Commented:
hey Blee,

Add a new textbox and set the control souce to

Iif([mesUnit] = "Pound",MaxInvty - InvtyOnHand) / 16,0,) +
Iif([mesUnit] = "Gallon",(MaxInvty - InvtyOnHand) / 128,0,)
MS Access Systems Creator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
Jack,
The report is base on a table, do I need to create a query for the report? Not quite sure by "reports recordsource", where do I put this Public Function? To pass the parameter for a text box in the report, do I just call it on "On Format".

Please go a little more slower, still learning as I go.

blee

Commented:
add a new textbox in your form, right click on it, select properties, select the data tab, and select Control source.  You can then use Jack's function in there (if you have the functionsotred in a module) and alt. You could use mine..

Flav's

Commented:
Flav's

The field Ttorder is a text box unbound in the report.
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
I would say that a query would always be better then a direct link to the table in nearly all cases.
The public function can be put in any public module of code on the modules tab, especially one designated for application specific functions
(function that only this app will use,... some functions are good in any app)

Where are the parameters coming from now?

Commented:
O.K

I create the query in the report. the text box "Ttorder" is an unbound text box; can it be unbound?

Also I put the code into a module as follow:

Public Function CalTotalOrder(MesUnit, MaxInvty, InvtyOnHand) As Long
Const lbPound As Integer = 16
Const galGallon As Integer = 128

If MesUnit = "Pound" Then
TtOrder = (MaxInvty - InvtyOnHand) / lbPound
ElseIf MesUnit = "Gallon" Then
TtOrder = (MaxInvty - InvtyOnHand) / galGallon
End If

End Function

Where in the report do I call this from and how?

blee
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
If you are going to use the code in a module you need to tell me where
MaxInvty, InvtyOnHand, and lbPound all comefrom

Commented:
MaxInvty, InvtyOnHand MesUnit are all from a table call "tblInvtyOrder".  lbPound is just a constant that I declare such as one pound = sixteen ounce.  Same thing with galGallon, 1 gallon = 128 fluid ounces. I just needed someway to convert back to pounds or gallons.

Ttorder is the total unit that need to be order. It is an unbound textbox on the report detail section.

Hope this help.

blee
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
what decides Pound/Gallon??
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
wow,  nevermind...

query column using renamed function GetTtOrder

ttorder: GetTtOrder([MesUnit],[MaxInvty],[InvtyOnHand],[lbPound],[galGallon])

Commented:
Pound and Gallon is coming from the field "MesUnit", each record is assign a unit of measurement.

Commented:
If I understand correctly, I create the following in a module:

Function GetTtOrder(MesUnit, MaxInvty, InvtyOnHand) As Long
Const lbPound As Integer = 16
Const galGallon As Integer = 128

If MesUnit = "Pound" Then
TtOrder = (MaxInvty - InvtyOnHand) / lbPound
ElseIf MesUnit = "Gallon" Then
TtOrder = (MaxInvty - InvtyOnHand) / galGallon
End If

End Function

Then in the report's  query put this in "ttorder: GetTtOrder([MesUnit],[MaxInvty],[InvtyOnHand],[lbPound],[galGallon]) " as a new field.

blee
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
Function GetTtOrder(xMesUnit, xMaxInvty, xInvtyOnHand,xlbPound,xgalGallon) As Long

notes: the parameters of the function have been changes to prevent conflict with the actual field names in the query.
normally all paramters would be TYPED to assure that the correct datatype was being passed to the function
parameters would be prefixed to indicate datatype like "i" for integer, "s" for strings etc

we're playing this on by ear...

Commented:
It's asking me to put the parameter of each value of the x's...fields.
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
sorry, should have explained,...
all the parameters used in the function need to be renamed to match those in the function header, put an x in front of each occurance of them
in the funtion as you see them in the changed header I posted.

Where were you being asked for values of the x's

Commented:
The following is what I put in my module;

Function GetTtOrder(xMesUnit, xMaxInvty, xInvtyOnHand, xlbPound, xgalGallon) As Long

Const xlbPound As Integer = 16
Const xgalGallon As Integer = 128

If xMesUnit = "Pound" Then
TtOrder = (xMaxInvty - xInvtyOnHand) / xlbPound
ElseIf xMesUnit = "Gallon" Then
TtOrder = (xMaxInvty - xInvtyOnHand) / xgalGallon
End If

End Function

Then in the query of the report, I put in a new field as follow :

ttorder: GetTtOrder([xMesUnit],[xMaxInvty],[xInvtyOnHand],[xlbPound],[xgalGallon])

Is this correct?
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
ttorder: GetTtOrder([MesUnit],[MaxInvty],[InvtyOnHand],[lbPound],[galGallon])

in the function they are parameters
in the query they are your field names

Commented:
Jack

It's still asking for the fields.
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
Where is the function

Show me the funtion and show the sql
MS Access Systems Creator
CERTIFIED EXPERT

Commented:
this question is now cross-linked the http://Q_20878142.html

Commented:
Thanks for the help

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.