Q. Please explain how to code a calculation in a report.

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

Jim Jones

Hi.

I have a report, whose data comes from a query.

Can you please demonstrate 2 things:

How to code my subtraction calculation in the query,

AND

How to code it in an unbound field, in the report design.

Since I've been wisely advised from you all not to keep a calculated
field in a table, I'd like to know how to do it in both of those ways.

Here's what I need:

Subform Field2 - (minus) subform Field1 = subform resultfield

I imagine the calculation for the report is the same set up in the
subform, and as such I'd like the user to see it on the subform before
they even preview the report.

Thanks,
Jim
 
Jim Jones said:
Hi.

I have a report, whose data comes from a query.

Can you please demonstrate 2 things:

How to code my subtraction calculation in the query,

AND

How to code it in an unbound field, in the report design.

Since I've been wisely advised from you all not to keep a calculated
field in a table, I'd like to know how to do it in both of those ways.

Here's what I need:

Subform Field2 - (minus) subform Field1 = subform resultfield

I imagine the calculation for the report is the same set up in the
subform, and as such I'd like the user to see it on the subform before
they even preview the report.

Thanks,
Jim
Click to expand...

As a calculated control in the query design:

ResultField: Field2 - Field1

As the controlsource expression of a calculated control on a form or
report:

=[Field2] - [Field1]

Bear in mind that, as calculated above, if either Field1 or Field2 is
Null, the result will be Null. You may want to have Nulls treated as
zeros in these calculations, in which case you'd modify the above to
something like

ResultField: Nz(Field2, 0) - Nz(Field1, 0)

and

=Nz([Field2], 0) - Nz([Field1], 0)
 
This method you provided only gives results for the first customer's
record. Not for each subsequent customer record.

How do I make each subsequent record (for the same customer), update,
when calculated that way?

Thanks,
Jim

Jim Jones said:
Hi.

I have a report, whose data comes from a query.

Can you please demonstrate 2 things:

How to code my subtraction calculation in the query,

AND

How to code it in an unbound field, in the report design.

Since I've been wisely advised from you all not to keep a calculated
field in a table, I'd like to know how to do it in both of those ways.

Here's what I need:

Subform Field2 - (minus) subform Field1 = subform resultfield

I imagine the calculation for the report is the same set up in the
subform, and as such I'd like the user to see it on the subform before
they even preview the report.

Thanks,
Jim
Click to expand...

As a calculated control in the query design:

ResultField: Field2 - Field1

As the controlsource expression of a calculated control on a form or
report:

=[Field2] - [Field1]

Bear in mind that, as calculated above, if either Field1 or Field2 is
Null, the result will be Null. You may want to have Nulls treated as
zeros in these calculations, in which case you'd modify the above to
something like

ResultField: Nz(Field2, 0) - Nz(Field1, 0)

and

=Nz([Field2], 0) - Nz([Field1], 0)
Click to expand...
 
Jim Jones said:
This method you provided only gives results for the first customer's
record. Not for each subsequent customer record.

How do I make each subsequent record (for the same customer), update,
when calculated that way?

Thanks,
Jim
Click to expand...

I have no idea what you mean. Which method did you use, the calculated
field in the query or the calculated control on the form/report? Either
method *should* display the correctly calculated value for each record,
so there's something different in your setup than I've been imagining.
Please describe exactly the tables, fields, queries, forms, reports, and
controls that are involved.
 
I have no idea what you mean. Which method did you use, the calculated
field in the query or the calculated control on the form/report? Either
method *should* display the correctly calculated value for each record,
so there's something different in your setup than I've been imagining.
Please describe exactly the tables, fields, queries, forms, reports, and
controls that are involved.
Click to expand...

The calculated control on the subform.
But, you know what? I think I got it to work, when I put the
expression in "Control Source" for the text box property.
I had the expression in something like default value, but I think I
got what I want now.

Thanks again,
Jim
 
Jim Jones said:
The calculated control on the subform.
But, you know what? I think I got it to work, when I put the
expression in "Control Source" for the text box property.
I had the expression in something like default value, but I think I
got what I want now.
Click to expand...

Yes, it's Control Source where it would have to go. Default Value
wouldn't cut it. I'm glad you worked it out.
 
Back
Top