How to Calculate and Round of to nearest 0.25?

[number2]

I need to calculate the DateDiff of 2 time feilds and then round to the nearest 1/4 hour (.25 of 60min)Any ideas?

 

[tomschafer]

Try this expression...

1/(10/(DateDiff("n", Date1, Date2)/6))

 

[number2]

I tried that and I get a blank field. Very strange. It worked when you tried it?

 

[number2]

OK I got it working. BUT.. I need it to round off the the nearest 0.25 (1.0 1.25, 1.5, 1.75)

 

[tomschafer]

This function will return what you are looking for. A couple of notes- 1. The finest granularity is minutes. 2. It will not work with 2 dates 5 minutes or less apart.


Function QuarterHour(dteDate1 As Date, dteDate2 As Date) As Double

Dim strString As String

strString = 1 / (10 / (DateDiff("n", dteDate1, dteDate2) / 6))

If InStr(strString, ".&quot > 0 Then
Select Case Mid(strString, InStr(strString, ".&quot, Len(strString))
Case 0 To 0.25
strString = Left(strString, InStr(strString, ".&quot) & "25"
Case 0.251 To 0.5
strString = Left(strString, InStr(strString, ".&quot) & "50"
Case 0.501 To 0.75
strString = Left(strString, InStr(strString, ".&quot) & "75"
Case Else
strString = Left(strString, InStr(strString, ".&quot - 1) + 1
End Select
End If

QuarterHour = strString

End Function

 

[number2]

Use this on the OnUpdate event?

 

[tomschafer]

Nope, in your original post you said "I need to calculate the DateDiff of 2 time feilds"- where are you putting this calculation a third text box? If so you would set the Control Source property of that box to QuarterHour([TextBox1], [TextBox2]). Or were you doing something else with the calculation.

 

[number2]

You understand exactly what I need, however I can not get it to work. Do I swap out the dteDate1 and 2 with the time field names?
Do I save this as a function?

 

[tomschafer]

Copy and paste the code into a module window exactly as is. Then you can call it as a function- So instead of-"Do I swap out the dteDate1 and 2 with the time field names?"- you can pass those field names as input parameters to the function. 2 examples of how you can apply this (Replace The Blue Text with the control names of your application)...
1. With a query...

select
QuarterHour([DateTime Field1], [DateTime Field2]) as RoundedQuarterHourDifference
from
........

2. On A Form....

If you have 3 textboxes- [DateTime Field1], [DateTime Field2] and [Calculated Difference]
You can set the Control Source property of [Calculated Difference]
to = QuarterHour([DateTime Field1], [DateTime Field2])

 

[number2]

Sorry for the bother; I appear to have done all correctly but I get a "#Name?" error.

I have checked it all out but I can not find the error. Feeling rather weak at the moment.