We help IT Professionals succeed at work.

Calculate in a report

blee17517
blee17517 asked
on
428 Views
Last Modified: 2010-08-05
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

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)
UNLOCK SOLUTION

Author

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

Author

Commented:
Flav's

The field Ttorder is a text box unbound in the report.
jadedataMS 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?

Author

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?

Thanks for your patience.

blee
jadedataMS 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

Author

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
jadedataMS Access Systems Creator
CERTIFIED EXPERT

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

Commented:
wow,  nevermind...

query column using renamed function GetTtOrder

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

Author

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

Author

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
jadedataMS 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...

Author

Commented:
It's asking me to put the parameter of each value of the x's...fields.
jadedataMS 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

Author

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?
jadedataMS 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

Author

Commented:
Jack

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

Commented:
Where is the function

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

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

Author

Commented:
Thanks for the help

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

Get Access
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

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

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

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
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

Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.