Huge problems with calculations based on subform/subreport

[Swiftraven]

Hi all,
I have a form which is based on 3 queries. I perform various calculation on the data and output it to the forms text boxes. I have created 2 invisible subforms to perform a lookup on a calculated value in a lookup table. In one subform I grab 2 values from the record above and in the other subform I get 2 values from a record below the calculated value, interpolate those values and use the result in the rest of the calculations. This works fine on the form when I just requery the subforms for each record.

My problem is that this does not work on a report. I have had no experience writing reports that do any calculations and it is showing. I tried to recreate the forms calculations on the report, with subreports instead of forms, but when I try and open the report, I am queried for the value that the subreports are based on (I have to input the value twice for some reason). Then the output for that particular record is correct on the report. When I tried to create a report on two records, I had to input the values 12 times by hand to get it to output...

I was wondering if there is a way to perform the calculations on the report like I did on the form or a way to transfer the results from the form into a report (the form can have many records to cycle through and perform calculations on)

Any help is GREATLY appreciated.
Jason

 

[67peshawar294]

Jason,
I have had similar instances on some reports.
In "design view" of the report look at the "Order By" box and see if it has a lengthy list of sort items. If it does delete them and your report, hopefully, will run normal.
Jim

 

[Swiftraven]

Jim,
I deleted them but I get an error that states "You must define a sort field or expression for the group header or footer in the report you tried to print or preview"

Jason

 

[67peshawar294]

Hmmm,
Try the "sorting and grouping" icon on the tool bar and set your sort and grouping there.
Jim

 

[Swiftraven]

Same thing, the Field/Expression field is blank and the Sort Order has Ascending (it will not let me delete it) and I get the error.
When I do have anything there, it still asks for the value that I want to run the subreport queries on. Not sure if this is the problem or not.
Been messing with this for a week and the due date is almost here. What a huge pain

Thanks
Jason

 

[67peshawar294]

Jason,
Click in the blank part of the "field expression" and choose the field for each sort you need. Below you can choose headers and footers.
Jim

 

[Swiftraven]

I have No selected for both Group Header and Group Footer.
I when I try and select a Field to sort on, none show up. I believe this is because I use if-then-else statements in the On_Open for the report to decide which query to run the report on. I put the part number field in by hand, but when I try and run it, it asks for the value in that textbox (txtPartNumber), then the other calculated value that it has been needing all along.

Jason