computed fields in tables, forms, reports

[uscitizen]

i have a table which for simplicity's sake has three fields. one is weight and the other is height and the third is bsa which is equal to the weight/(height*height; the third one we'll call bsa. the more mathematically gifted will recognize this as weight divided by height squared, but anyway....

i originally created the table with the three fields, and generated a form from the table. after sprucing up the form, i used it (save ---> report) to create a report.

then something happened: i decided i'd make it easier for the person entering the data and get the software to computed the bsa.

so i modified the bsa control in the form and replaced the 'bsa' in the control source field with "=[Weight]/([Height]*[Height])". i also got a little carried away and modified the 'Format' property of this field to be 'General Number' (I think it was empty aforehand). I left the Number of Decimal places on "Auto" however.

Now when I run the report (which if you're eyes haven't glazed over) that you recollect was created using the spiffy "Save Form into Report" capability, I nothing in the BSA. The numbers I'm using were Weight 150, Height 60. On the form, the computed BSA value is 0.042 (although if I were to increase the width of the displaying control there'd be quite a few more decimal places worth of numbers).

Just to wrap this up in the interest of completeness: in the report, the properties of the 'bsa' field is bsa in the Control Source property. The Format is not completed, and the Decimal Places is set to Auto.

So, I guess my question is what's going on?

 

[JSD]

Hello

Replacing the 'bsa' control source with a calculation detaches that field from the query driving the form/ report. Instead, put the calculation in your query under the 'bsa' criteria.

Hope this helps...

Jeremy

 

[uscitizen]

we're almost there - i haven't got a query. do you mean to say, create a query which computes bsa from the two fields and pop the queries 'bsa' field into the form and the report or something?

 

[JSD]

Hello

Yes, something like that. I created a sample db and figured out what to do.
Create a query with your height and weight. You won't need the bsa field in the query if you only want to display its value in a form/ report. In fact, you don't even need the bsa field in your table.
In your newly developed query, put your expression in a new 'Field:' exactly as you see it below. After you type it in and click out, 'Expr1:' will automatically appear in front of your expression.

([w])/([h]*[h])

Then, change the format property for the field to 'standard' to get some decimal places.

To view your results, create a form or report using this query. Keep going...

Jeremy

 

[uscitizen]

to no avail on this end still.....

my hash of what all this good assistance is leading to went something like this.

following through on jeremy's idea, i created a query virtually the same as his, excepting mine uses the control names 'weight' and 'height'. closed out of the query and went to my report textbox field which i want to display the 'bsa' value. i used the expression builder button and drilled down to the query i just finished creating which holds the 'Expr1' field. I pasted the 'Expr1' into the expression builder's window and clicked out. I noticed that the Control Source value is not "=[Physical Examination Query]!Expr1". I just checked and the Format for this field's been set to 'Standard' and I note the Decimal Places property's 'Auto'. SO - why when I go to run the report do I get an "Enter Parameter Value" pop-up displaying with the text "Physical Examination Query" and why do I get an '#ERROR' in the textbox's window when I look at the report results??

I think I'm going to be scratching my head this weekend.

 

[JSD]

Hello

Give me your e-mail address and if you like I'll send you the sample I built. Otherwise, I'll get back to you later after my guitar lesson...

Jeremy