Calculating labour time, incl. VAT, excl. VAT and VAT amount

[Compressor]

How to go about this?

I have a couple of fields:
Starttime and Endtime -> difference is stored in TimeLength
That part I could figure out on my own and seems to work nice.

But this I can't:
I have a combobox with a couple of values in it called Tariff (data type: Number, Field size: Double, Format: Euro, Decimal places: 2)
Another combobox with a couple of values in it called VATTariff (data type: Number, Field size: Double, Decimal places: Auto)
A textbox called LabourInVAT
A textbox called LabourEXVAT
A textbox called LabourVATAmount

(Access stores time as one large number instead of normal time, do I need to take that into consideration also?)
Based on the values selected in Tariff, VAT and TimeLength, there need to pop up some numbers in the corresponding textboxes (labour inclusivVAT, exclusiv VAT and the VAT amount itself, which BTW in NL is normally 19% but can vary depending of what product/service you provide, or if your business/sales "goes" outside of NL, so that's why the different values are possible. Same thing goes for the Tariff itself, but that's self-explanatory.)
BTW VAT is called BTW in NL. Which I found funny to mention...

 

[gemma-the-husky]

bascially in the after update event for the combo box, you need to say

labour ex vat = timediff * combobox
etc

However, you might need to change this depending on how you present the combo box data.

You also need to consider how you store this in your timerecord. If the labour rates, or the BTW rates change in the future, your calculations will be different, so you probably need to store these values in the timerecord. You shouldn't need to store the results of the caslcs, because you can always produce these when you need them.

Time difference is effectively stored as a fractional day, and will be correct assuming its all working time

eg start 9.30 am, finish 2.30 pm will correctly store the time spent as 5hrs - its just a matter of deciding how to present it.

 

[Compressor]

ow man... I'm so glad to hear you say that I need to store these values in this case. That's quite a relief you know that? Really. Because of all the normalization issues I've read about I was kinda scared to bashed around the forum so to speak for doing this

The values presented in the VAT box are 0% 6% and 19% and are presented as such via a combobox.
I'm just not sure how to go about the result from the "TimeLength" box.

The code I'm using for each appointment is:

Private Sub Ctl1stAppointmentEndTime_LostFocus()
Me.[Ctl1stAppointmentLength] = Format([Ctl1stAppointmentStartTime] - 1 - [Ctl1stAppointmentEndTime], "Short Time")
End Sub

So if Starttiime is 10:00 and Endtime is 12:00 the ApLength = 2:00. And is presented in the box as such.
But then?
I really can't get it straight in my head. Maybe that's because I'm sick... but I'm afraid I'm just not good at this kind of stuff.
Can I just say labourcosttotal= Aplength * Tariff, won't this come up with all sorts of errors since the 2:00 hour figure is a non standard number? The same goes for all the other fields of course...

Before I'm really gonna try to code this for all fields and totals etc. I will need to learn something about basic calculus with VBA, so that's what I will be doing this weekend. So it might get somewhat clearer then. But to really learn, I want to be able to experiment a bit, for that, I will need to know the correct syntax... and that's why I'm so confused about the time number. Or will access just "eat" that statement without errors?
BTW I use 24hour format time... don't know if that matters at all.

 

[gemma-the-husky]

no, you can use time as any other number and say aplength * tarriff. Although the text box displays the time as a 2:00, it stores it internally as just a number. If you display it as a number instead of as a time format, you will see something different.

You might find it treats the 2.00 hours as a fractional day (ie 1/12 day), and you may need to multiply the result by 24 therefore - just try it and see.

----------------------------
just thinking about the normalization issues. If you don't capture and store the rates as at the time you use them, then you will have to create a rate history table, so that you can lookup the rate as it was at the date in question. Its a matter of taste. On something like a charging record, I would certainly capture and save any appropriate rates at the time of entry (or invoicing if that makes more business sense).

 

[Compressor]

Maybe a stupid question:

Edit:

Yes... a stupid question. Never mind

 

[Compressor]

Well... that was easy enough. I didn't expect to just be able to add or divide or xxx like (well, almost) you would write it on a piece of paper.

But I did bump into one problem though.
6 possible visits to a customer each with a different timelength. Using these lengths to create the amounts for each visit is no problem. But I also have a field TotalTimeSpentOnCase, when I want to add all the hourse that I worked on a case, it comes up with several errors. I even tried the GetElapsedTime function from the MS website: http://ask.support.microsoft.com/kb/210604 using this syntax:

Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + [Ctl3rdAppointmentLength] + [Ctl4thAppointmentLength] + [Ctl5thAppointmentLength] + [Ctl6thAppointmentLength])

but this also came up with some errors, first invalid use of null, so I changed all fields to a default value of #0:00:00# which took care of that error. But then another came up, and another.... that function just won't allow me to do waht I want with this one. I think the problem lies in the fact that I'm trying to add 6 "short time" fields into one other "short time" field, which ofcourse, will never hold 48 hours, since it is based on a 24 hour date/time type. I am right in thinking that?
If so....
How do I solve this?

 

[gemma-the-husky]

a better way of dealing with the blanks is to wrap each one in an nz
ie nz(appointmentlength,0) turns the blank into a 0 so it can be added, without the nulls propagating through.

What error message are you getting with your short-time add? I would have thought it would accept times over 24hrs, but it may be that causing the problem.

 

[Compressor]

a better way of dealing with the blanks is to wrap each one in an nz
ie nz(appointmentlength,0) turns the blank into a 0 so it can be added, without the nulls propagating through.

What error message are you getting with your short-time add? I would have thought it would accept times over 24hrs, but it may be that causing the problem.

The normal add or the getelapsedtime add from MS?

 

[gemma-the-husky]

you said this expression gave you errors

Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + [Ctl3rdAppointmentLength] + [Ctl4thAppointmentLength] + [Ctl5thAppointmentLength] + [Ctl6thAppointmentLength])

instead of setting defaults to 0, wrap each of these in nz(fldname,0). Do you still get an error with the add.

[Edit] note - setting the defaults in the table won't change any values currently stored, so this may not have fixed the addition problem at all.

 

[Compressor]

wrapping them in such manner, how?
Default property set to for example: nz(Ctl1stAppointmentLength,0)
or setting that property on the on_load or on_current event of the main form like
Me.Ctl1stAppointmentLength = nz(Ctl1stAppointmentLength,0)

Sorry, but I do not know so need to ask...

 

[Compressor]

Right now, I have removed the default values in those fields. I have a cmdCalcTotalTime On_Click event with

Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + [Ctl3rdAppointmentLength] + [Ctl4thAppointmentLength] + [Ctl5thAppointmentLength] + [Ctl6thAppointmentLength])

With timelengths set for: AppointmentLength1, 2 and 3 totalling 27 hours.
Using the getelapsedtime method when clicking the cmdButton the error is Invalid use of Null. When clicking on Debug, the pointer aims at the GetElapsedTime module window, on the line: days = Int(CSng(Interval))

So "wrapping"(?) them in the NZ value might work, but I haven't yet figured out how to do that.

 

[Compressor]

Private Sub cmdCalcTotalTime_Click()
If Nz(Ctl1stAppointmentLength, 0) <> vbNullString And Nz(Ctl2ndAppointmentLength, 0) <> vbNullString And Nz(Ctl3rdAppointmentLength, 0) <> vbNullString And Nz(Ctl4thAppointmentLength, 0) <> vbNullString And Nz(Ctl5thAppointmentLength, 0) <> vbNullString And Nz(Ctl6thAppointmentLength, 0) <> vbNullString Then
Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + [Ctl3rdAppointmentLength] + [Ctl4thAppointmentLength] + [Ctl5thAppointmentLength] + [Ctl6thAppointmentLength])
End If
End Sub

??

Also comes up with Invalid use of Null (error 94) and points to GetElapsedTime module window, on the line: days = Int(CSng(Interval))

But I'm just guessing... I suppose that's not what you meant.

 

[Compressor]

Ow... shiet.... it might be of value to say that the times calculated in the length fields are done with the format command,

Me.[Ctl6thAppointmentLength] = Format([Ctl6thAppointmentStartTime] - 1 - [Ctl6thAppointmentEndTime], "Short Time")

not the getelapsedtime function

 

[Compressor]

Hey! just saying:

Me.TimeSpentOnCase = Ctl1stAppointmentLength + Ctl2ndAppointmentLength + Ctl3rdAppointmentLength + Ctl4thAppointmentLength + Ctl5thAppointmentLength + Ctl6thAppointmentLength

Doesn't come up with anything by the way. No errors, but also no values. Sow... Aarrghh..!?

 

[Compressor]

Just placing, for example, Nz([2ndAppointmentLength,0]) in the default value fields doesn't appear to have any effect either. Not positive or negative. For now, I've set the default times to 0:00 now, since that seems to be the thing it wants to get presented with. As soon as I know how to apply the NZ correctly I will.

Edit: I've created a text box with a bind set to a Double Number type called TestNumber (made field TestNumber in a table).

When the length of the appointments is 23:59, the value returned in the number field is 0,999305555555556
When the length of the appointments is for example 23:59 + 0:01 then the number returned is 1
When the combined length of appointments is for example 23:59 + 0:01 + 2:30 then the number returned is 1,10416666666667

So the count does continue. After having done this I returned to the old situation: the textbox, but this time with a general date type setting.

When the length of the appointments is 23:59, the value returned in the general date field is 23:59:00
When the length of the appointments is for example 23:59 + 0:01 then the value returned is 31-12-1899
When the combined length of appointments is for example 23:59 + 0:01 + 2:30 then the value returned is 31-12-1899 2:30:00
When the combined length of appointments is for exampel 23:59 + 0:01 + 2:30 + 23:59 the value returned is 1-1-1900 2:29:00
Which all seem correct. I hope this tells something more about the situation. I think I need to capture the 1,10416666666667 and tell access to keep counting upwards instead of upping the date or whatever it is that it is doing. I had this feeling, so that's why I tried using the GetElapsedTime Function but when I use that function it comes up with the 2113 error, the value you have entered for this field isn't valid. And the error points to the Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + etc.
So how to capture and format it now?

It's so strange, because when I found the MS example and tried it in an empty testform, the result was nicely put as N hours, N minutes, N seconds.

 

[Compressor]

Edit: Sorry, I did something wrong with posting, so I am reposting my previous post.

Just placing, for example, Nz([2ndAppointmentLength,0]) in the default value fields doesn't appear to have any effect either. Not positive or negative. For now, I've set the default times to 0:00 now, since that seems to be the thing it wants to get presented with. As soon as I know how to apply the NZ correctly I will.

Edit: I've created a text box with a bind set to a Double Number type called TestNumber (made field TestNumber in a table).

When the length of the appointments is 23:59, the value returned in the number field is 0,999305555555556
When the length of the appointments is for example 23:59 + 0:01 then the number returned is 1
When the combined length of appointments is for example 23:59 + 0:01 + 2:30 then the number returned is 1,10416666666667

So the count does continue. After having done this I returned to the old situation: the textbox, but this time with a general date type setting.

When the length of the appointments is 23:59, the value returned in the general date field is 23:59:00
When the length of the appointments is for example 23:59 + 0:01 then the value returned is 31-12-1899
When the combined length of appointments is for example 23:59 + 0:01 + 2:30 then the value returned is 31-12-1899 2:30:00
When the combined length of appointments is for exampel 23:59 + 0:01 + 2:30 + 23:59 the value returned is 1-1-1900 2:29:00
Which all seem correct. I hope this tells something more about the situation. I think I need to capture the 1,10416666666667 and tell access to keep counting upwards instead of upping the date or whatever it is that it is doing. I had this feeling, so that's why I tried using the GetElapsedTime Function but when I use that function it comes up with the 2113 error, the value you have entered for this field isn't valid. And the error points to the Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + etc.
So how to capture and format it now?

It's so strange, because when I found the MS example and tried it in an empty testform, the result was nicely put as N hours, N minutes, N seconds.

 

[gemma-the-husky]

sorry, been out a fair while

your original statement was -
Me.TimeSpentOnCase.Value = GetElapsedTime([Ctl1stAppointmentLength] + [Ctl2ndAppointmentLength] + [Ctl3rdAppointmentLength] + [Ctl4thAppointmentLength] + [Ctl5thAppointmentLength] + [Ctl6thAppointmentLength])

ignoring the fgetelapsedtime function
use nz like this

Me.TimeSpentOnCase = nz([Ctl1stAppointmentLength],0) + nz([Ctl2ndAppointmentLength],0) + nz([Ctl3rdAppointmentLength],0) + nz([Ctl4thAppointmentLength],0) + nz([Ctl5thAppointmentLength],0) + nz([Ctl6thAppointmentLength],0)

basically if you add anything to a null, you get null as the answer, so you only need 1 null for the equation to return a null answer.

the nz converts any null to whatever you want, in this case 0, so that you can add values normally. if the value is not null, it leaves it unchanged.


so the sum can now add values correctly.

 

[Compressor]

Hi again,

Tnx. I did that now. Nice to know the value is automatically set to the type of variable that the field holds. At first I thought it would just place a 0 in the field, but now I see it places 0:00, as in short time.

But still... it outputs the values up to 23:59 and then goes back to 0 again, so 48 hours for example will never become the sum of all those Ctl fields in the SpentonCase field.

How can I make it so that the value of 25 hours can be reached in the SpentTimeOnCase field? Or 1 day and 1 hour in the output formatting?

 

[gemma-the-husky]

not done anything like this before, so I wasn't sure - you are right the time stops at 24.00 hours (it actually does register a day by the way, but that only turnsd it into a strange date, so thats no good.)

However, just treat the total box as a number, now - I set the total textbox format to a fixed number, 3 decimal places

I then added 3 times of 12 hours, 12 hours, 2 hours and it gives me 26.000 (hours).

Try that.

 

[Compressor]

ok, i've gone into the table, set the type to number, double, fixed, 3 decimals.
Same on the textbox in the form.

When I add the hours (12+12+12) now, I get 1,50 ?
For 1.5 days of course... haha, but how do I get it to say 36?