Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Values (2K SP-3)

    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

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    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... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    HTH

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    Many thanks for the successful direction and referenced reading.

    Tom

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Values (2K SP-3)

    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.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    I tried the following
    =DSum(

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Passing Values (2K SP-3)

    Try taking away the square brackets aound the word RoomPRoration.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Values (2K SP-3)

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

    <pre>=DSum(
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    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
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Values (2K SP-3)

    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.

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    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
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Values (2K SP-3)

    Tom,

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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Values (2K SP-3)

    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.
    Attached Files Attached Files

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Values (2K SP-3)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •