Round up issues on reports (1 Viewer)

OnlyTD · Aug 1, 2018

Hi
I have created a database to record the room hire at our centre.
I have an input form based on a query using only the booking table, where the user enters the details of the booking, dates times number of bookings etc and calculated fields work out the sub total, discount, Vat net total, vat and amount due.

It then produces a booking form based on a query using several tables including the booking table and the calculations for sub total, discount, Vat net total, vat and amount due are made again.
My initial problem is that the report is rounding the amount due up and the form is not.
My second question is am I doing these calculations in the correct places and should the results be stored in the bookings table?
I have tried to do the calculations directly in the table but the datediff was not allowed and the discount % is not stored at that level it is stored on the Client table as it depends on who the client is.

Thank you in advance for any advice you can offer.

Ranman256 · Aug 1, 2018

you will want to store all 'values' of the calculations in the table.
At form input, store as much as you can on the table:
Qty 2,
Price 8.50
Total 17.00
discountPct 10%
discountAmt 1.7
ExtTotal 18.70

Mark_ · Aug 1, 2018

2nd to Ranman's post. This is one of the few cases where storing a "Calculated" value is important. You need to show all amounts charged for a given transaction and the total. You will not want to change history if you fix something in the future.

I'd do all of your calculations on the form, make sure they are proper, and only report 'data' on the report, not do calculations.

What this probably means for you is you'll want to make a function in your form that does all of the calculations so you have ONE place to look if you have issues.

Gasman · Aug 1, 2018

PMJI, but for my benefit please.
I see the need for

Qty 2,
Price 8.50
discountPct 10%

but why the others?
Is that just to simplify report creation, in that nothing needs calculating again?
Also wouldn't total be 17 - 1.7

TIA

Mark_ · Aug 1, 2018

@ Gasman,

Only reason to save each is in case something does change in code. During an audit, it is horribly bad to have something that looks like what Ranman posted be on the receipt but not reflected in your database.

In context, his post shows WHY you would actually save them. Otherwise you have a receipt out there that cannot be reproduced in your program. Hate to be the one trying to explain that...

And for the record you are correct. Discount is reduced from total, not added to. Means if someone put it in backward you'd want to see it in the data.

OnlyTD · Aug 2, 2018

Hi Thank you for your replies.
Mark I wish I had your reply sooner, we have had price changes which is why I had to relook at my set up, I did such a major workaround, as in IFF calculations everywhere for before the date and one for after the date of change - headwork!!!

Next step: To store the calculated values in the underlying table fields do I need to use code?

Mark_ · Aug 2, 2018

OnlyTD,

Depends on how complicated your calculation is. IF you expect it will become complex then have a Sub that handles your calculations/fills the fields. Also have your sub use a large comment block to clearly show what reference you are using for calculations (including reference to any rounding requirements) so that future coders know why it works as it does.

This helps avoid issues where there are legal requirements to round to a specific number of decimals when doing calculations, primarily tax.

I've been fortunate to not have to certify payment calculations for a system. I HAVE had to audit incentive payments and had to issue recoupment on over $15 million though. I learned the hard way how bad it can be when the government states "Do it this way" and it isn't done "This way".

This also means your future self will be able to point to a set of data and clearly state "From this date to this date, we calculated VAT per <Legal citation here>. On this date, when <Legal citation here> took effect, we change to <How changed> and it is reflected in our data.

Not sure if you do need to get that picky, but it helps avoid issues if your finance people at least have a change record.

OnlyTD · Aug 2, 2018

Hi, sorry Mark

these are the calculations I am using on my form at the moment, what do you mean by sub?

BIO_Total_Duration:
(DateDiff(“n”,[BIO_End_Time]-[BIO_Start_Time]))*[BIO_Number_Of_Occurences]/60

Sub Total
BIO_Sub_Total: IIf([BIO_Status_Ref]="C",0,[BIO_Total_Duration]*[BIO_Room_Cost_Per_Hour])

Amendments
BIO_Deduction:
IIf([Billing_Discount]="No",0,IIf([Billing_Discount]="Yes",([Booking_Sub_Total]*0.4),([ Booking_Sub_Total]*0.5)))

Net total (exc Vat)
BIO_Net_Total_exc_vat: [BIO_Sub_Total]-[BIO_Deduction]

Total_Vat_Amount
BIO_Vat_Total: IIf([BIO_Vat]=0,0,[BIO_Net_Total_Exc_Vat_Tbl]*0.2)

Total Amount Due
BIO_Total_Amount_Due: [BIO_Total_Cost_exc_vat]+[BIO_Vat_Total ]

OnlyTD · Aug 6, 2018

Hi
I have put sent the following values from the form to the underlying table:
time duration
discount rate
vat rate

would you send the other calculations such as sub total, vat total and amount due to the table or because I have sent all other values there, calculate them in the table too?

thank you

This has been a great help.

Mark_ · Aug 6, 2018

To answer what I ment by a "Sub"

Code:

SUB TCO_Calculations
'Comment on why BIO_Total_Duration is calculated using the below
   BIO_Total_Duration = (DateDiff(“n”,[BIO_End_Time]-[BIO_Start_Time]))*[BIO_Number_Of_Occurences]/60

'Comment on why Sub Total is calculated as below
   IF Bio_Status_Ref <> "C" Then
      BIO_Sub_Total = BIO_Total_Duration * BIO_Room_Cost_Per_Hour
   'ELSE
      'What ever the ELSE would be, if needed.
   END IF
.
.
.
.
.
.
END Sub

You would call this any time you need to calculate your values. It would do them all at once, but if any are dependent on other calculated values put them below so they have the proper value to work with. The comments are there for other programmers to understand what/why.

When dealing with money, this is also where you would put notes in regarding who said to do what how to meet regulatory requirements. This would include things like "Round to two decimal points per XYZ so it meets legal requirement Blah, blah, blah."

Simple calculations wouldn't be saved unless there is a chance they would appear different in reported values. Things like "SubTotal + TaxTotal = InvoiceTotal" would not need to be saved, but check with your finance people to see if they would want it saved.

That is the difficult part; how big is your fiscal liability if something is wrong? What will you need to provide (or your customers) if there is an issue and they get audited? Since what is charged is now "History" and should never be changed, this is mostly dependent upon what your regulatory environment is like.

OnlyTD · Aug 6, 2018

Hi Mark

I had put the calculations in the query the sub form was based on and then added the following to the after update of the room selection combo to send the form information to the table.

Private Sub cboRoom_Type_ID_AfterUpdate()
Me.BIO_Room_Cost_Per_Hour = Me.cboRoom_Type_ID.Column(2)
Me.BIO_Vat = Me.cboRoom_Type_ID.Column(3)
BIO_Vat_Rate = Me.frmBIO_Vat_Rate
BIO_Total_Duration = Me.frmBIO_Total_Duration
BIO_Discount_Rate = Me.frmBIO_Discount_Rate

End Sub

What you have shown me is so much better and neater I will be looking at this on Thursday when I am back in work.

As for financials I am not sure of our obligations so I will check this out on Thursday too.

I am so grateful. I will let you know how it goes on Thursday!

Mark_ · Aug 6, 2018

I hope it goes very well for you and you find out you DON'T need this level of audit compliance. If you ever do, just keep it in mind.

NOTE: If you were not talking about amount charged but what WILL BE, I'd say just show the calculated value and not worry about saving it. Since this is what has been charged making sure you are compliant for audits becomes a bigger issue.

OnlyTD · Aug 9, 2018

Hi
I have tried your suggestion above and in the after event of time fields and number of session fields I have put the datediff for the occurrence which is working fine.

I then put the following in the after update event of the room type selection combo:

Private Sub cboRoom_Type_ID_AfterUpdate()

'Sub Total is £0 if the booking has not been cancelled
BIO_Room_Cost_Per_Hour = Me.cboRoom_Type_ID.Column(2)
BIO_Vat = Me.cboRoom_Type_ID.Column(3)

If BIO_Status_Ref <> "C" Then

BIO_Sub_Total = BIO_Total_Duration * BIO_Room_Cost_Per_Hour
Else
BIO_Sub_Total = 0
End If

'Discount calculation '

If txtDiscount_ptr = "No" Then
BIO_Discount_Rate = 0
Else
If txtDiscount_ptr = "Yes" Then
BIO_Discount_Rate = 0.4
Else
BIO_Discount_Rate = 0.5

End If

'Sub total calculatons

BIO_Discount_Total = BIO_Sub_Total * BIO_Discount_Rate
BIO_Net_Total_Exc_Vat = BIO_Sub_Total - BIO_Discount_Total

'Vat calculation'

If BIO_Vat = 0 Then
BIO_Vat_Rate = 0
Else
BIO_Vat_Rate = 0.2
End If

BIO_Vat_Total = BIO_Net_Total_Exc_Vat * BIO_Vat_Rate

End If

End Sub

which seems to work initially but if I try to change the room type I receive a runtime 3164 error, field cannot be updated.

Mark_ · Aug 9, 2018

Is the control locked?

OnlyTD · Aug 9, 2018

No its not locked and its enabled.
It works first time but the second time (if the room changes), the error comes up with this line highlighted:
BIO_Sub_Total = BIO_Total_Duration * BIO_Room_Cost_Per_Hour

but the room cost per hour does change, its the other fields that don't, they are all not locked or enabled.

Mark_ · Aug 9, 2018

Do you have a stripped down copy of your DB with just the tables and this form you can upload?

OnlyTD · Aug 9, 2018

I have looked into this and think it is because the total fields are calculated in the table.
I cannot find a way to change these, so I need to leave them as calculated in the table only?
I only wanted the calculation all in the sub of the after update event because they were all in one place but if I cannot change it then I will leave the calculations where they are.

OnlyTD · Aug 9, 2018

I have added information to Company booking BT John.
the first time I add a room type works fine but the calculated fields on the after update event eg Vat rate in the table will not update if the room type changes

Mark_ · Aug 9, 2018

OK, items I've noted...
1) Ever table should have an auto number primary key. This avoids issues when data changes since you will be linking them by a field that will never be edited.
2) I would change all of your calculated fields to be data. Have the calculation done by the form, not in the table structure.
3) Switch from using macros to VBA. You will quickly find that VBA is far more useful and easier to troubleshoot than macros.

I will see if I can find some time this afternoon to redo this portion to be easier to work with.

The reason you don't want tables to control calculated values or have embedded lookups is because it moves WHERE a value is calculated away from where the data is input. It also means you run into issues when data cannot be put in together or when you need to change back ends.

OnlyTD · Aug 10, 2018

thank you Mark - this would be a great help, the more the company relies on this database the more concerned I am that it is not robust enough.

Round up issues on reports (1 Viewer)

Registered User.

Well-known member

Longboard on the internet

Enthusiastic Amateur

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

Registered User.

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

Registered User.

Attachments

Longboard on the internet

Registered User.

Similar threads

Users who are viewing this thread