Round up issues on reports (1 Viewer)

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi

I am using the line below in my vba but it is not working for the minutes, eg half hour etc

BIO_Total_Duration = (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) * [BIO_Number_Of_Occurences] / 60

the code used to work when it was in the underlying query of the form.
 

Mark_

Longboard on the internet
Local time
Today, 08:19
Joined
Sep 12, 2017
Messages
2,111
Where are you putting it?
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi Mark

it is in the after update event of each of the fields - BIO_Start_Time, BIO_End_Time and BIO_Number_Of_occurences.
 

Mark_

Longboard on the internet
Local time
Today, 08:19
Joined
Sep 12, 2017
Messages
2,111
OK, if you are doing DateDiff to return a number of minutes, why are you dividing by 60?

To see what I mean, you may want to show what [BIO_Start_Time] and [BIO_End_Time] hold, then what datediff is returning first.

Code:
msbgox "Starting " & BIO_Start_Time & " ending " & BIO_End_Time & " DateDiff returns " &  (DateDiff("n", [BIO_Start_Time], [BIO_End_Time]))

Once you make sure DateDiff is returning what you want, then make sure [BIO_Number_Of_Occurences] has a valid value.

Walk it through until you find what isn't working.
 

Cronk

Registered User.
Local time
Tomorrow, 02:19
Joined
Jul 4, 2013
Messages
2,770
Can the form's source query be updated?
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi

Thank you for your help in this I am nearly there, I have walked it through as Mark suggested. This is my code and it works fine now:

Private Sub cboRoom_Type_ID_AfterUpdate()

BIO_Room_Type = Me.cboRoom_Type_ID.Column(1)
BIO_Room_Cost_Per_Hour = Me.cboRoom_Type_ID.Column(2)
BIO_Vat = Me.cboRoom_Type_ID.Column(3)
BIO_Total_Duration = (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) * [BIO_Number_Of_Occurences] / 60

'Debug.Print BIO_Occurence_Duration = DateDiff("n", [BIO_Start_Time], [BIO_End_Time]) \ 60 & "." & Format(DateDiff("n", [BIO_Start_Time], [BIO_End_Time]) Mod 60, "00")
Debug.Print "Starting " & BIO_Start_Time & " ending " & BIO_End_Time & " DateDiff returns (Minutes per session) " & (DateDiff("n", [BIO_Start_Time], [BIO_End_Time]))
Debug.Print "Starting " & BIO_Start_Time & " ending " & BIO_End_Time & " DateDiff returns minutes/60 (hr) " & (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) / 60
Debug.Print "Total Time " & BIO_Start_Time & " to " & BIO_End_Time & " no times " & BIO_Number_Of_Occurences, (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) / 60 * [BIO_Number_Of_Occurences]
'Vat calculation'
If BIO_Vat = 0 Then
BIO_Vat_Rate = 0
Else
BIO_Vat_Rate = 0.2
End If
'Sub Total is £0 if the booking has not been cancelled
If BIO_Status_Ref <> "C" Then
Else
BIO_Sub_Total = 0
End If

'Discount calculation '
If txtDiscount_ptr = "No" Then
BIO_Discount_Rate = 0
ElseIf txtDiscount_ptr = "Yes" Then
BIO_Discount_Rate = 0.4
Else
BIO_Discount_Rate = 0.5
End If
'Calulations'
BIO_Sub_Total = BIO_Total_Duration * BIO_Room_Cost_Per_Hour
BIO_Discount_Total = [BIO_Sub_Total] * [BIO_Discount_Rate]
BIO_Net_Total_Exc_Vat = [BIO_Sub_Total] - [BIO_Discount_Total]
BIO_Vat_Total = [BIO_Net_Total_Exc_Vat] * [BIO_Vat_Rate]
'Total'
If BIO_Status_Ref = "C" Then
BIO_Total_Amount_Due = 0
Else
BIO_Total_Amount_Due = [BIO_Net_Total_Exc_Vat] + [BIO_Vat_Total]

End If


End Sub

All results are recorded in the underlying table.

At the moment I have the calculations on the After Update event of the room selection combo, but I need to recalculate on any field changes such as if a time is amended or the number of sessions, Where would you recommend putting them?

Thank you.
 

Cronk

Registered User.
Local time
Tomorrow, 02:19
Joined
Jul 4, 2013
Messages
2,770
Put the code in a separate routine in the Form's code module. Call the routine from any control where the value is an input to the calculation, from the after update event of each control.
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi Cronk

thank you for your reply. I am sorry but although I understand the concept of using the module I don't know how to create a separate routine, I have been looking on line but I haven't found anything that not too in depth, can you point me in the right direction please?
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi All

I am still working on this issue and I am pleased with my progress.

the formulas work although I have still to put them in a routine (haven't quite worked that one out yet) but at the moment they are working on one of the form fields after update event.
My issue is rounding up. I have:
sub_Total = Total_Duration * Room_Cost_per_hour
Discount_Total = Sub_Total * Discount Rate
Net_Total_exc_Vat = Sub_Total - Discount_Total
Vat_Total = Net_Total_exc_Vat * 0.2
Total_amount_Due = Net_Total + Vat_Total

In my tables I have all fields (sub_Total, Discount_Total, Net_Total, Vat_Total, Total_Amount_due) set as:

‘data type = number’,
‘field size = double’,
‘format = currency’,
‘Decimal places – 2’

In my input form I have all fields[TY1] currency, auto.
But for a Net_Total_exc_Vat of £30.84 I am still getting Vat of £6.17 where I need to get £6.16,

Please Help!!
[TY1]
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi

does anyone have any advice on my previous question please?
 

JHB

Have been here a while
Local time
Today, 16:19
Joined
Jun 17, 2012
Messages
7,732
..
But for a Net_Total_exc_Vat of £30.84 I am still getting Vat of £6.17 where I need to get £6.16,
Why do you need £6.16, (30.84*0,2=6.168), please clarify?
 

OnlyTD

Registered User.
Local time
Today, 08:19
Joined
Jul 26, 2018
Messages
42
Hi

I need £6.16 because the client is told that the cost is £18.50 per hour including VAT, but the invoice total for 2 hours is £17.01.

Thank you
 

isladogs

MVP / VIP
Local time
Today, 15:19
Joined
Jan 14, 2017
Messages
18,186
Hi

I need £6.16 because the client is told that the cost is £18.50 per hour including VAT, but the invoice total for 2 hours is £17.01.

Thank you

Does that calculation make any sense to you? It certainly doesn't to me
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:19
Joined
Sep 21, 2011
Messages
14,056
Try your calculation as follows.
Excel workbook with formula attached

Take gross value and divide by 1.2 to get Net
Calc VAT by multiplying Net by 0.2

Now round both as required and report.

HTH
 

Attachments

  • VAT Calc.xlsx
    9.1 KB · Views: 84

Users who are viewing this thread

Top Bottom