Problem with negative time calculation 1

[djjay]

I'm developing a system which is used for calculation of employees hours worked and rates of pay. I currently have a query which is calculating the hours worked between a [StartTime] and [EndTime] primarily using the DateDiff function.

This works great most of the time however on some occasions employees are working nightshifts for example 19:00 - 7:00 the following day. Therefore my [HoursActive] field is displaying -12 and my [PayAmount] field is showing -£60.00 (based on a £5 per hour rate variable).

There may be a simple solution but i simply want the system to work these out as normal hours and not negatives so there are no negatives in the system. But the same section must work for normal hours on the same day and those overlapping over a 00:00 time slot. I hope someone can help!

J

 

[Crowley16]

instead of using just the time value, use a general date value, this will include the date and time of startTime and endTime, and when you use datediff, it'll show the actual difference...

however you may have to format the output a little, but that shouldn't pose a problem...

--------------------
Procrastinate Now!

 

[djjay]

ok i'll give that a try...so is there no easy way to just change a value in a field if it's negative to positive? I know it's not such good practice was just hoping i wouldnt have to go through my whole system and make loads of changes

just me being lazy

 

[Crowley16]

well you could always try:

if val < 0 then
val = val * -1
endif

--------------------
Procrastinate Now!

 

[djjay]

Ok guys im still having loads of probs with this. My access Knowledge is a bit shot to pieces as its a while since i've used it.

Here's a few more details about what im trying to do (apologies if I've misunderstood your directions already Crowley16):

I have a Project Bookings form. On this form there is a subform which is based on a query using a table named [BookingDates]. This [BookingDates] form contains different fields relating to the date of a particular booking which is linked to the project booking (each project may have a number of bookings). It also contains a field [StartTime] and [EndTime] and [BookingAmount] and [BookingRate].

As it is a query i also have a field in the query with the following in the field column:

BookingAmount2: Round(CCur(Nz(DateDiff("n",[StartTimeInitial],[EndTimeInitial])*[BookingRate]/60,0)),2)

This creates a field named [BookingAmount2] which is used on the subform. I also have fields [Minutes]

Minutes: DateDiff("n",[StartTimeInitial],[EndTimeInitial])

and [Hours]

Hours: [Minutes]/60

On the subform I then have an unbound text box with the control source

=Sum(Nz([BookingAmount2]))

Altogether this calculates the total hours worked and the amount which is then copied over to the [BookingAmount] field with a button using the SetValue method.

The only problem i have is the negative values when my startime and endtime pass through midnight. IF you could advise me how to change my code or how i could impliment the other code to change the values that would be appreciated.

Sorry For the long post

J

 

[Crowley16]

in your minutes field, use a iif(datediff(blah), datediff(blah), datediff(blah) * -1)

--------------------
Procrastinate Now!

 

[djjay]

Thanks Crowley16! Finally got it sorted I think i was just missing the point a bit!!

Changed the minutes field and the bookingAmount field so it simply calculated [hours]*[rate] which then followed the calculation through wonderfully!

Just completed the change throughout the system where similar code is used and no probs so far

Star for you mate!

 

[Crowley16]

just as a side note...

there is an ABS function, (it's in native vba, so you should be able to use it in queries and controls but I've not tested it) which will just give you the +ve number all the time...

e.g. abs(-1) == 1

maybe easier than what I've said before, only rememberd just this minute...

--------------------
Procrastinate Now!