Results 1 to 10 of 10
  1. #1
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11

    Is VBA possible in a report?

    In my database report I am summing values to come up with a subtotal that the customer may be charged. (screenshot below)
    Then we add in taxes & discounts and come up with the grand total.
    For values that are over a certain dollar amt. per item, the value that is taxed is capped per some states law. (for example, lets say some state laws say for line items over $3000, the tax can only be charged on the first $3000)
    In these instances I need to be able to cap the dollar amt when coming up with the subtotal so I can figure out the correct taxes.
    Is this possible to do in the report? I cannot seem to figure out how to add vba in the expression builder.



    Click image for larger version. 

Name:	subtotal.png 
Views:	32 
Size:	4.5 KB 
ID:	46037

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Shouldn’t need vba, you should be able to do it in your record source as a tax calculation

  3. #3
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11
    Quote Originally Posted by Ajax View Post
    Shouldn’t need vba, you should be able to do it in your record source as a tax calculation
    Since the capped value only effects the tax calculation, I need to use the actual value to come up with the actual subtotal a customer may be charged.
    Then using the same values (with some being capped), I need to create a secondary subtotal that is used only to calculate the taxes.
    Is this possible in the record source? (I hope this makes sense)

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Consider a second field (column) showing the taxable amount (max allowed or the actual amount if less using query calculated field and IIF expression) then a 3rd that calculates the tax based on column 2, then sum only the tax values. Wouldn't that make it clearer to everyone what the tax sum is based on? Otherwise, someone is applying 6% to the full amount and wondering why it doesn't jive?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11
    I have been trying to implement your suggestion but I am guessing on where I need to add these fields
    I have tried adding fields to the query for the report but did not have any success.
    I also added an unbound textbox (called cappedAmt) to the report with an expression that will cap the amounts to $5000 - this worked shown in the attached
    I then added another unbound textbox and tried to sum the cappedAmt but this does not work - is this because the values are not available since they are calculated when opening the report?

    Where do you suggest I add the "query calculated field and IIF expression"
    Click image for larger version. 

Name:	Untitled.png 
Views:	22 
Size:	11.5 KB 
ID:	46102

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    query calculated field means/meant a calculated field in the query. Sorry I was not clear on that.
    Perhaps mock up a test db with whatever is required and post it here for direct assistance? See top of forum toolbar if not familiar with how to attach files. Or try creating the calculation in the query and use any such calculated fields in the report.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like this for your recordsource

    Code:
    SELECT Charges, iif(Charges>5000,5000,Charges)*0.06 AS Tax
    FROM myTable

  8. #8
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11
    Thank you all for the suggestions.
    I added a calculated field to the query that caps the amount charged per line item
    Then I added a textbox to sum the calculated field which is used to calculate the surtax.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure where you are in the world and may not be relevant to you, but most sales based taxes are based on each line, not on the total - if only because an invoice might (now or in the future) contain taxable and non taxable items or taxed at a different rate. Calculating on the sales total will potentially give a small difference, only a few cents perhaps but a difference all the same. A few years ago a well known burger vendor was caught calculating on the total rather than the individual line (resulting in a saving to them). But for the volume they were doing, that amounted to a lot. Resulted in a very expensive audit and a large penalty fine (plus repayment).

    So before you commit to your chosen route, do make sure you are complying with the appropriate tax rules.

  10. #10
    TomAtNMR's Avatar
    TomAtNMR is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2021
    Location
    Florida
    Posts
    11
    before you commit to your chosen route, do make sure you are complying with the appropriate tax rules.
    Thanks for the heads up, I didn't think about this at all.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-16-2018, 07:43 PM
  2. Replies: 16
    Last Post: 03-13-2018, 05:24 PM
  3. Replies: 9
    Last Post: 07-03-2017, 11:24 PM
  4. Replies: 4
    Last Post: 12-09-2015, 09:02 AM
  5. Replies: 3
    Last Post: 03-11-2013, 05:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums