Passing Values (2K SP-3)

Topic

New Reply

I currently have a rptNCOver90Days which uses a union query, qryNCTotalTaxExempt as its
data source. One of the fields in the query is RoomProration. I have another rptNCTaxReport
which uses the sum of the RoomProration as a field in the rptNCTaxReport.

Currently, the operator runs a RoomProration report and inputs the sum of the RoomProration
field into the proration field in tblPropertyName via an input form, frmEnterOver90Days.

What I want to accomplish is to eliminate the manual entry of the sum of the RoomProration
field. in tblRental. When rptNCTaxReport is run, it would have already gotten the sum of the
room Proration field from qryNCTotalTaxExempt.

Since RoomProration is a calculated field, I guess there is no reason to even store it in
tblPropertyName in the first place.

Any suggestions would be appreciated.

Tom

Reply | Quote

Replies

I’m assuming there is already a text box control in the report design that gets it’s value from input value in tblPropertyName…
If so, just change the control source property to:
= DSum(“[RoomProration]”, “qryNCTotalTaxExempt”)

If you want to read more about this, lookup Domain Aggregate functions in Access Help…
HTH

Reply | Quote

Many thanks for the successful direction and referenced reading.

Tom

Reply | Quote

I have a related question in that we also have a consolidate database that loads multiple locations in order to be able to run consolidated reports. In the consolitated version, qryNCTotalTaxExempt extracts all NC locations by facility. I know I can’t use DSum because that will give me the sum of all locations. In the consolidated database, what I need to pass is the sum of each individual location instead of the aggregate sum.

Suggestions anyone?

Tom

Reply | Quote

On the one hand, the domain aggregate functions such as DSum have an optional third argument that acts as where-condition:

DSum(“field_name”, “table_or_query”, “where-condition”)

So if you group a report by location, you can put a text box with control source like

=DSum(“[RoomProRation]”, “qryNCTotalTaxExempt”, “[LocationID] = ” & [LocationID])

On the other hand, you can probably create a totals query that groups by location and sums RoomProRation.

Reply | Quote

I tried the following
=DSum(

Reply | Quote

Try taking away the square brackets aound the word RoomPRoration.

Reply | Quote

Try taking away the square brackets aound the word RoomPRoration.

Reply | Quote

You need either a Chr(34) or single quote on each side of the concatenated value:

=DSum(

Reply | Quote

I tried your suggestion but can’t get it to work. I am attaching a scaled down version of the database.

Any assistance wuld be greatly appreciated.

Tom

Reply | Quote

It’s hard to test, since the union query doesn’t return any records, but there were some problems with the DSum expression. The spaces and brackets were not all ib the correct places, and you have to refer to the name of the control bound to PCity, not to the field name. This seems to work:

=DSum(“[RoomProration]”;”qryNCTotalTaxExempt”;”[PCity]=” & Chr(34) & [Text56] & Chr(34))

Finally, the text box must be in the detail section, since its value depends on a control in the detail section.

Reply | Quote

Hans,
Many thanks for the assistance. I am attaching a database which now should be working.

In trying to add a total line to the report, I have encountered a couple of problems:
1. Since I have recalculated in the total line, there are some rounding errors e.g. Local
Tax Amount $19.90 vs $19.91
2. The accommodations tax rate (stored in tblPropertyName) is 6% in Lexington and
3% in the other 3 jurisdictions. My total for the Accommodation is using the 3%
rate for all.
3. Should I be using a Total Query as the source for rptNCTaxReport instead of
qrySalesTaxNC, which you gave as one possibility on 12/10/03?
4. If so, I don

Reply | Quote

Tom,

I haven’t forgotten you, but getting it all right will take a bit of time. Please be patient.

Reply | Quote

Tom,

I haven’t forgotten you, but getting it all right will take a bit of time. Please be patient.

Reply | Quote

1. and 2. You can use (invisible) text boxes in the group footer to accumulate the values. Their control source is (in theory) the same as those of the visible text boxes in the group footer, but their Running Sum property is set to Over Groups. For the text boxes containing taxes, round the result in the invisible text box to 2 decimal places (not the format, but the result of the formula.)

3. and 4. The present query should work.

5. The queries could probably be made more efficient, in particular qryRentCalc3 – it seems to contain repetitions of the same formula.

I have attached a modified version of your database.

Reply | Quote

Thank you so much for the amount of time it took to produce the attachment. I am most appreciative. I will try to apply the principals to my other tax reports.

Regards,

Tom

Reply | Quote

Thank you so much for the amount of time it took to produce the attachment. I am most appreciative. I will try to apply the principals to my other tax reports.

Regards,

Tom

Reply | Quote

1. and 2. You can use (invisible) text boxes in the group footer to accumulate the values. Their control source is (in theory) the same as those of the visible text boxes in the group footer, but their Running Sum property is set to Over Groups. For the text boxes containing taxes, round the result in the invisible text box to 2 decimal places (not the format, but the result of the formula.)

3. and 4. The present query should work.

5. The queries could probably be made more efficient, in particular qryRentCalc3 – it seems to contain repetitions of the same formula.

I have attached a modified version of your database.

Reply | Quote

Hans,
Many thanks for the assistance. I am attaching a database which now should be working.

In trying to add a total line to the report, I have encountered a couple of problems:
1. Since I have recalculated in the total line, there are some rounding errors e.g. Local
Tax Amount $19.90 vs $19.91
2. The accommodations tax rate (stored in tblPropertyName) is 6% in Lexington and
3% in the other 3 jurisdictions. My total for the Accommodation is using the 3%
rate for all.
3. Should I be using a Total Query as the source for rptNCTaxReport instead of
qrySalesTaxNC, which you gave as one possibility on 12/10/03?
4. If so, I don

Reply | Quote

It’s hard to test, since the union query doesn’t return any records, but there were some problems with the DSum expression. The spaces and brackets were not all ib the correct places, and you have to refer to the name of the control bound to PCity, not to the field name. This seems to work:

=DSum(“[RoomProration]”;”qryNCTotalTaxExempt”;”[PCity]=” & Chr(34) & [Text56] & Chr(34))

Finally, the text box must be in the detail section, since its value depends on a control in the detail section.

Reply | Quote

I tried your suggestion but can’t get it to work. I am attaching a scaled down version of the database.

Any assistance wuld be greatly appreciated.

Tom

Reply | Quote

I tried the following
=DSum(

Reply | Quote

On the one hand, the domain aggregate functions such as DSum have an optional third argument that acts as where-condition:

DSum(“field_name”, “table_or_query”, “where-condition”)

So if you group a report by location, you can put a text box with control source like

=DSum(“[RoomProRation]”, “qryNCTotalTaxExempt”, “[LocationID] = ” & [LocationID])

On the other hand, you can probably create a totals query that groups by location and sums RoomProRation.

Reply | Quote

I have a related question in that we also have a consolidate database that loads multiple locations in order to be able to run consolidated reports. In the consolitated version, qryNCTotalTaxExempt extracts all NC locations by facility. I know I can’t use DSum because that will give me the sum of all locations. In the consolidated database, what I need to pass is the sum of each individual location instead of the aggregate sum.

Suggestions anyone?

Tom

Reply | Quote

Many thanks for the successful direction and referenced reading.

Tom

Reply | Quote

I’m assuming there is already a text box control in the report design that gets it’s value from input value in tblPropertyName…
If so, just change the control source property to:
= DSum(“[RoomProration]”, “qryNCTotalTaxExempt”)

If you want to read more about this, lookup Domain Aggregate functions in Access Help…
HTH

Reply | Quote