Time Calculation

[dttate66]

It gave me a "Syntax error" when running and stopped and highlighted yellow this part;

"Public Function TimeDurationAsDate(pvFrom As Variant, pvTo As Variant) As Date"

David

 

[Mark_]

OK, cleaned it up and THIS TIME I DIDN'T FORGET TO COPY THE ENDING END FUNCTION...Grrrr....:banghead::banghead::banghead::banghead::banghead:

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

 

[dttate66]

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.

David

 

[isladogs]

David

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

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

 

[dttate66]

Thanks Colin for the info., I will definitely keep some this in mind going forward.

David

 

[Mark_]

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!

David

Hmmm... Steak dinner in Vancover... but your info shows Texas. Some how I think it would be better in Texas....

 

[MarkK]

Ha, ha, I got your steak dinner!
Mark

 

[dttate66]

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.

David

 

[dttate66]

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.

 

[isladogs]

Well I'm not Mark but there must be a flaw somewhere in your function(s).

It works absolutely fine doing those times using my code (SMUG FACE!)

NOTE: Because the end time is set as 00:00:00, Access just shows the date in the screenshot

 

[dttate66]

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

 

[isladogs]

Don't worry - I wasn't really expecting you to change it now

However, I was interested to see if I had the same issue (& pleased that I didn't)

You now need to review the logic in your original function(s)

 

[Mark_]

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.

Because if you pass the code I set up a null OR a zero, it returns zero. Do you need it to work only with time values?

 

[Mark_]

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.

 

[dttate66]

Yes, that is what I need, to recognize 12:00 am as a valid time. How and where do I change the NZ's?

David

 

[Mark_]

The first two checks.

If Nz(pvFrom, 0) = 0 Then afError = 1
If Nz(pvTo, 0) = 0 Then afError = 1

becomes

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.

 

[dttate66]

Thanks Mark, you really have been a tremendous help, that took care of the problem.

P.S. I still owe you that steak dinner wherever you are, but yes, they are better here in Dallas.

David

 

[Mark_]

David,

I'd have to let you know when I'm in your "Neck of the woods". Have a friend down in Hutto.

 

[dttate66]

Close to Austin, I know where it is. Just give me shout, there is plenty of Texas Roadhouse Steak houses' around that area.

David

 

[dttate66]

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?

David