calculated fields in a report using 2 variables from a form + how to make sum?

[Mmy1981]

Hi,
I have a report (+ subreport) based on 2 DataTables.

In the detail section of the subreport (with a field "price") I want to calculate somthing for every record. This field is not a part of the DataTable. Depending of a value in a form I can write a function to calculate this (for example -> I make a function "extra cost" that does: price * form1.textbox1.text"

Now I have 2 big problems...
- How can I use form1.textbox1.value in my report? (do I have to pass it as a variable?)
- I can't make a sum of this function (in the report footer I need to calculate the sum of the function "extra function).

Can anybody help me, caus I really don't know where to start. In Access I bypassed this problem by making a table-make query. In this query I could refer to a function for every record calculating this. In the end, I used this new table to generate the report.

[Mmy1981]

Can anybody help me with this problem?
I found something about passing variables to a report, but then I still can't make a sum of a function field...

[SunshineBH]

If i understand your problem correctly it will be best to do the calculation required inside your SQL query that you are using to do the report.

eg
@Textbox as integer
Select fields, price * @Textbox as calcfield
from table

and then use calcfield and not the function on your report.
Just pass your form1.textbox1.text to the SQL stored procedure

Hope this helps

[Mmy1981]

SunShineBH, thanks for the reply...

but this calculation needs several lines of code (select case method, some if-blocks, ...). Is that possible in an sql query?

at this moment i'm trying the following:
1) add 2 extra fields (A and B) in the original table used for the calculation
2) update the original table giving A and B the value of textbox1.text and textbox2.text
3) use this table to generate the report
4) make a function, using field A and B to calculate
5) I think (and hope!) I can make a sum using the function I'v created in step number 4

But maybe is working with stored procedures better like you said, but I have no idea how this works. And i'm not sure I can use "select case" and "if" in a function over here

[SunshineBH]

You can use "case" etc in a sql stored procedure. Books-online should give you a good idea how these work. Maybe also have a look at "cursors" these can be quite powerful (if a little slow) as well.

However, if it gets too complicated, how about creating a temp table with the result of the calculation. And then just use the temp table in your query. This might be easier to do and maintain but the downside will be a longer running report.

[Mmy1981]

I tested with creating a temp table and this seems to work...
1) create a duplicate from the original table
2) add columns A and B to this duplicate
3) update this table, giving A and B the values from textbox1 and textbox2
4) generate the report with this new tabel and write a function that calculates with the value in A, B, ...
5) now it's possible to make a sum of this function in the report footer.

OR... I can just add column A and B to the original table and update them when needed. These columns are hidden but I can use them in the report for calculations...

I'll try to do some research about stored procedures, but I'm affraid it will become very complicated since the function I need to write is quite long. It has to check "textbox1" (now column A), "textbox2" (column B), and 5 columns in the original table.