OK, cleaned it up and THIS TIME I DIDN'T FORGET TO COPY THE ENDING END FUNCTION...Grrrr....:banghead::banghead::banghead::banghead::banghead:
Code:
Public Function TimeDurationAsDate(pvFrom As Variant, pvTo As Variant) As Date
Dim afError As Integer
Dim adReturn As Date
afError = 0
adReturn = 0
If Nz(pvFrom, 0) = 0 Then afError = 1
If Nz(pvTo, 0) = 0 Then afError = 1
If IsDate(pvFrom) = False Then afError = 1
If IsDate(pvTo) = False Then afError = 1
If afError = 0 Then
If pvTo < pvFrom Then
If Int(pvFrom) + Int(pvTo) = 0 Then pvTo = pvTo + 1
End If
adReturn = pvTo - pvFrom
End If
TimeDurationAdDate = adReturn
End Function
Oh man!, Mark you are a genius! it worked, left all those #Error's blank and my reports ran perfectly! I owe you a great big Steak Dinner if I ever come to Vancover!, but I did notice one thing it is doing, from 12:00am to 8:00am, it shows "0", or from any number to midnight the same thing.
Obviously it's your database and you are free to do what you want
Having said that:
1. It's easy to concatenate separate fields for use in the functions.
E.g. Use a query with a field
Code:
StartDateTime: [StartDate] & " " & [StartTime]
2. Conversely you can start with one field and just extract the date part for filtering your report ...or whatever
3. Whether you use my functions or not, if you don't want to see zeroes on your report where one of the times is blank, you can use code to replace with an empty string or conditional formatting to 'hide' it e.g white text on white background
Mark
See point 3 above. But 0.00 can also be shown as 0 anyway
EDIT
Just posted and saw Mark's latest reply. Excellent.
Suggest you mark the thread as solved
Oh man!, Mark you are a genius! it worked, left all those #Error's blank and my reports ran perfectly! I owe you a great big Steak Dinner if I ever come to Vancover!
Obviously I am dealing with two different Marks?? Either way, so maybe one of you that created the code to get rid of my #error, can tell my why a time to midnight (10:00pm to 12:00am) shows "0" until I put it a minute before or after to have it read the Man Hours.
Obviously I am dealing with two different Marks?? Either way, so maybe one of you that created the code to get rid of my #error, can tell my why a time to midnight (10:00pm to 12:00am) shows "0" until I put it a minute before or after to have it read the Man Hours.
Yeah, Mark and MarKK have been helping alot on this, it would take too much to change the whole database and all its structure to your code, plus, I HAVE to have the dates and times in separate fields for many reasons
Obviously I am dealing with two different Marks?? Either way, so maybe one of you that created the code to get rid of my #error, can tell my why a time to midnight (10:00pm to 12:00am) shows "0" until I put it a minute before or after to have it read the Man Hours.
I've got a little app I am playing with to demonstrate how SQL / ACCESS actually deals with date/times. Please see the attached.
Unless I know exactly what you plan to pass to a function, it can be really difficult to do proper error checking. If you wish to pass times and have them work, change the nz checking on the passed values to IS NULL checking. That way it will accept 12:00am as a valid time (converts to 0) without considering it an error.
If Nz(pvFrom, 0) = 0 Then afError = 1
If Nz(pvTo, 0) = 0 Then afError = 1
becomes
Code:
If IsNull(pvFrom) = True Then afError = 1
If IsNull(pvTo) =True Then afError = 1
As a generic piece of code I wasn't sure if you were going to be mixing and matching date/times, time values, and NULL values. You will have to do some checking when data is entered to make sure what YOU want stored gets stored if someone enters data then deletes it from a field.
Mark,
When I have access minimized to where only the forms or reports show, I noticed it does not let you use the shortcut menu (right-click drop down), is there a way to force it?