I want to make calculation in Report.
like this..

quantity....rate....Amount
-------------------------
12.........3675.00...44100.00
12.........2100.00...25200.00
10..........630.00....6300.00
------------------------------
34.........6405.00...75600.00...>(after this more addition possible)
add 100.......1.50.....150.00
------------------------------
.........................75750.00
add 15% .............13362.50
------------------------------
........................89112.50
add 20%..............17822.50
-------------------------------
Grand total.........106935.00

when we generate normal report it make total sum
after this three more addition possible in access
report ?

Please help to generate report in Access as like above ?

regards

Mateen

Mateen,

What I think you are asking for:

Is to be able to take a value in your report subtotal and to add a value (calculated) to the subtotal amount.

1. Ok, so in your report design, go the subtotal section and locate the text box with your subtotal item in it.
2. Copy the text box control.
3. Paste it straight away again... so literally: Ctrl-C and then Ctrl-V.
4. You should now have another copy of the text box below your original.
5. Select the new text box, open up properties and select the Data tab.
6. Make your calculation in the "Control Source" property.

I suggest just playing about a bit so you can see for yourself how it works, best way to learn imho.

Hope it helps,

Lee

Hi,

   Assume you already have these text boxes showing up on the report in the Detail section:

txtQTY txtRate txtAmount


   Add some text boxes to your report in the page footer, and then type this directly into each text box (format the text boxes for currency or standard, and set the decimal place to 2):

For the total quantity (txtTotQTY): =Sum([txtQTY])
For the total rate (txtTotRate): =Sum([txtRate])
For the total Amount (txtTotAmount): =Sum([txtAmount])

This will give you the line that says:

34.....6405.00....75600.00

I am not sure what you mean when you say "after this more addition possible" since you can't do that on a report, only a form. If the data is on the report, then you can add a text box, and Sum that into the total amount the same way as above.

Then to add 15%, if you want to show what 15% is, add two more text boxes, and type this in (after proper formatting):

For 15%: =Sum([txtTotAmount]*.15)
For the total amount plus 15%: =Sum([txtTotAmount]*1.15)

To add 20% more, do the same as with the 15% additions, and this will give you your grand total as well.

Did that work? This assumes one group of records per report or report page.

mmcdonal

Thanks for your response.
it is good idea copy paste the text box.
I also try sum like this.

(QUANTITY): =Sum([QUANITY])
(RATE): =Sum([RATE])
(AMOUNT): =Sum([AMOUNT])

but it give
"syntax error query expression first(quantity):sum([quantity])"

I also try like this.

15%: =Sum([Amount]*.15)

it give message

"extra in query express 15%: =Sum([Amount]*.15)"

it not add the 15% amount in the total amount.

in my grand total amount box grand total sum like this.

=Sum([AMOUNT])/([AMOUNT Grand Total Sum])

how add the 15% in the grand total sum ?

regards

Mateen

I am not sure why you have this:

(QUANTITY): =Sum([QUANITY])
(RATE): =Sum([RATE])
(AMOUNT): =Sum([AMOUNT])

You can't sum quantity in the quantity text box. When you create the text box, type only this in the text box:

=Sum([Quantity])

That should work. Put the totals in a footer or header, not in the detail section.

To ADD 15% to a total, you multiply it by 1.15, not .15 as in your example.

HTH



mmcdonal

Thanks you for your help.

it is working.
I use like this.

=Sum([Amount]*1.15)
it is ok.

I have two more queries.

1. I have to add three different amount after sum.
I also use three more box like this

=Sum([Amount]*[])
=Sum([Amount]*[])
=Sum([Amount]*[])

but it only one time ask enter parameter value.
it should ask three time enter parameter value how ?
what syntax ?


2. second query, I use sum like this

=Sum([Amount]*[])

when report run it ask enter parameter value
I want that add message like this
"enter amount to add"
when report run it ask "enter amount to add"
what syntax will use for display the message in this?

=Sum([Amount]*[])


regards

Mateen

Hi,

   I am not sure what you are doing with this code:

=Sum([Amount]*[])

   If you are trying to take parameters and pass them to these text boxes, what I would suggest is to create a module, and in that module, create Public variables to hold these values.

   Then when the user launches the report, before the report is called, open an input box to take your variables.

   So if you declare in the module:

Public lFirstParameter, lSecondParameter, lThirdParameter As Long

   Then when they click the button to launch the report, do this:

lFirstParameter = InputBox("Please enter the ? Parameter.")
lSecondParameter = InputBox("Please enter the ? Parameter.")
lThirdParameter = InputBox("Please enter the ? Parameter.")

   Then on your report, refer to the variable name in the On Format event of the area of the report where the text box is. So if it is in the Page Footer, select the Page Footer properties, then select the Event tab, then open the code for the On Format event.

   (You may have to put a text box on the footer that has this code:
    =Sum([Amount]) and make it invisible - call it txtSumAmount)

   In the On Format event, for your three visible text boxes, use this code:

Dim sAmount As Long

sAmount = Me.txtSumAmount

Me.txtSumOne = sAmount * lFirstParameter
Me.txtSumTwo = sAmount * lSecondParameter
Me.txtSumThree = sAmount * lThirdParameter

HTH



mmcdonal

Thanks for your response.

I try your coding, but I could not properly create the module.
it will more better that if can you send step by step. ie
1. create moudle and write this code...
2. open report in design mode and edit this property etc.and write code...
3.....

regards

Mateen

Hi,

  Go to the Modules group, and click New. Then in the module, type this code:

Public l1Param, 12Param, l3Param As Long

   This will create public variables for which you can set values with certain events without having to Dim them in each event, and this also makes the values transferable to the next event.

   Then to capture a value for each of these variables using Input Boxes, on the button Click event, type this code:

l1Param = InputBox("Please enter the ? Parameter.")
l2Param = InputBox("Please enter the ? Parameter.")
l3Param = InputBox("Please enter the ? Parameter.")

   I use a ? because I don't know the name of the parameter you are trying to capture with these variables.

   Then on your report on the On Format event of the section where you are making your caluclations using these parameters, probably on the page footer, create the text boxes to hold the values, like this:

txt1Param
txt2Param
txt3Param

   Format them for currency, and then add this code to the On Format event of the report section:

Me.txt1Param = =Sum([Amount]*l1Param)
Me.txt2Param = =Sum([Amount]*l2Param)
Me.txt3Param = =Sum([Amount]*l3Param)

   This will multiply the amount times the variable value.

   Perhaps you want to Sum the [Amount] field first and then put it in a text box that is invisible, type this is in the text box =Sum[Amount], call the text box txtAmount, then do this in the On Format event:

Dim lAmount As Long

lAmount = Me.txtAmount

Me.txt1Param = =Sum(lAmount*l1Param)
Me.txt2Param = =Sum(lAmount*l2Param)
Me.txt3Param = =Sum(lAmount*l3Param)

   Did that help?









mmcdonal

Thanks a lot for your help.
It is very helpful coding.

Regards

Mateen