Problem Concatenating DateDiff Values

[matthewnsarah07]

I have a form for calculating working hours with TOILDate as Date, TimeStart as Date and Time Finish as Date

To allow for night shifts, where TimeFinish is less than TimeStart the following vb is used :

'Night Shift Fix - Test'
Private Sub Command91_Click()
If Me.TimeFinish < Me.TimeStart Then
Dim StartTime As Date
Dim EndTime As Date
Dim EndDate As Date
Dim DiffHour As Integer
Dim DiffMin As Integer
EndDate = Me.TOILDate + 1
StartTime = Me.TOILDate & " " & Me.TimeStart
EndTime = EndDate & " " & Me.TimeFinish
DiffHour = DateDiff("h", [StartTime], [EndTime])
DiffMin = DateDiff("n", [StartTime], [EndTime]) - (DateDiff("h", [StartTime], [EndTime]) * 60)
Me.TotalAdd = [DiffHour] & ":" & [DiffMin]

Me.TotalMultiple = (Me.TotalAdd * 1.5)
Else
End If

End Sub

This basically creates two value like 13/12/2010 12:00 (adding a day to the finish time) then performs a datediff and finally strings the two values to fit in a DateTime field.

This works fine except when it get an error number which produces a runtime -###### - how can I stop the errors occuring as this code functions otherwise??

 

[vbaInet]

I've not really scrutinised your code but ##### in most cases isn't an error. It just means the control's width is too small to show the full text. Increase the width of your control and see if that still occurs.

 

[matthewnsarah07]

Sorry should have been more clear

I meant the runtime error message shows something like
Runtime Error -2147352567 (80020009) - Not valid for this field

Which I assume is an odd number, tends to occur when usinf odd time like
13/12/2010 22:06 to 14/12/2010 06:00 - works fine with most other times??

 

[vbaInet]

13/12/2010 22:06 to 14/12/2010 06:00 - works fine with most other times??

I don't see how that date/time is odd? Can you explain?

What line does it highlight when that error occurs?

 

[matthewnsarah07]

Thats what I didn't understand - it highlights

Me.TotalAdd = [DiffHour] & ":" & [DiffMin]

It seems to be certain times (minutes) that stop it working - any ideas

 

[vbaInet]

Have you tried manually inserting that value into the field via the form?

 

[matthewnsarah07]

If I do the math manually you mean and just type the result it then yes its fine

I'm assuming a value must be wrong on the DateDiff Output but it should have been straight forward - appears to be a minus for some reason

 

[vbaInet]

Let's try this:

Me.TotalAdd = TimeValue([DiffHour] & ":" & [DiffMin])

 

[matthewnsarah07]

Just stopped the VB Code at the error and say for 22:06 to 06:00 next day
it is showing DiffHour as 8 which is right but DiffMin as -6. This is causing the error, is it something to do with the calculation that forms DiffMin

 

[matthewnsarah07]

Sorry responded as you did - shows data type mismatch with that code - could me the minus number

 

[vbaInet]

Then swap things around to get rid of the minus.

 

[matthewnsarah07]

DiffMin = (DateDiff("h", [StartTime], [EndTime]) * 60) - DateDiff("n", [StartTime], [EndTime])

Swapped around as you day and works fine now - thanks for your help working this out, seems so simple now

 

[vbaInet]

Glad we could help. Test that a bit more though.

 

[matthewnsarah07]

Funny you should say that - just tried 22:00 to 06:26 and its thrown out a minus!

 

[vbaInet]

I still haven't looked at the logic of your code but if you're sure that without the minus it is returning the right value then do this:

Me.TotalAdd = [DiffHour] & ":" & Replace([DiffMin], "-", "")

 

[matthewnsarah07]

Not sure why the minus occurs but I've actually added in If Statement so if DiffMin is less than 0 it uses the original calculation and that seems to have negated the problem

 

[vbaInet]

Alright, still keep testing

 

[Brianwarnock]

Not sure why the minus occurs but I've actually added in If Statement so if DiffMin is less than 0 it uses the original calculation and that seems to have negated the problem

Depeding on whether the Stattime minutes are greater than the Endtime minutes or vice versa and which way round you do the calculation here

DiffMin = DateDiff("n", [StartTime], [EndTime]) - (DateDiff("h", [StartTime], [EndTime]) * 60)

then you can end up with a minus.
How about Abs will that work for you.

DiffMin = Abs(DateDiff("n", [StartTime], [EndTime]) - (DateDiff("h", [StartTime], [EndTime]) * 60))

OOPs No that nor your If is the answer , if the Endtime minutes are less than the Starttime minutes you will need to adjust the Hours.

Brian

 

[Brianwarnock]

I think that the normal way to do this is to do the calculation in minutes and then convert to Hours and Minutes.

Brian