Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-09-2018, 06:17 AM   #16
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Round up issues on reports

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

Mark_ is offline   Reply With Quote
Old 08-09-2018, 08:15 AM   #17
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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 is offline   Reply With Quote
Old 08-09-2018, 09:39 AM   #18
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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
Attached Files
File Type: accdb Skeleton Version OnlyTD.accdb (984.0 KB, 7 views)

OnlyTD is offline   Reply With Quote
Old 08-09-2018, 11:23 AM   #19
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Round up issues on reports

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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
OnlyTD (08-10-2018)
Old 08-10-2018, 01:34 AM   #20
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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.
OnlyTD is offline   Reply With Quote
Old 08-14-2018, 05:52 AM   #21
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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.
OnlyTD is offline   Reply With Quote
Old 08-14-2018, 07:56 AM   #22
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Round up issues on reports

Where are you putting it?

Mark_ is offline   Reply With Quote
Old 08-14-2018, 08:04 AM   #23
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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.
OnlyTD is offline   Reply With Quote
Old 08-14-2018, 12:41 PM   #24
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Round up issues on reports

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.
Mark_ is offline   Reply With Quote
Old 08-14-2018, 06:47 PM   #25
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: Round up issues on reports

Can the form's source query be updated?
Cronk is offline   Reply With Quote
Old 08-28-2018, 07:55 AM   #26
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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.
OnlyTD is offline   Reply With Quote
Old 08-28-2018, 10:15 PM   #27
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: Round up issues on reports

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.
Cronk is offline   Reply With Quote
Old 08-29-2018, 06:07 AM   #28
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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 is offline   Reply With Quote
Old 09-07-2018, 04:38 AM   #29
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Re: Round up issues on reports

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 is offline   Reply With Quote
Old 09-10-2018, 08:54 AM   #30
OnlyTD
Newly Registered User
 
Join Date: Jul 2018
Posts: 28
Thanks: 1
Thanked 0 Times in 0 Posts
OnlyTD is on a distinguished road
Unhappy Re: Round up issues on reports

Hi

does anyone have any advice on my previous question please?

OnlyTD is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Issues over emailing reports Brainfade Macros 4 06-02-2014 05:03 AM
Reports and query date issues lite4d Reports 3 04-03-2014 07:35 AM
To round or not to round (calculated fields) malcolmedward Tables 4 01-22-2013 12:34 PM
CSV Issues in Crystal Reports 2008 MaddMatt Crystal Reports 0 02-21-2008 07:30 AM
combo/reports issues swift Reports 1 01-22-2008 12:10 PM




All times are GMT -8. The time now is 08:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World