Elapsed Time Difference when time is > 24 Hours

[caddy36]

I'm doing a calculation in a Formula that works fine until I reach the 24 hour mark which makes my forumla invalid.

Can someone tell me what I need to do to update the statement to account for times greater than 24hours?

My Statement:
time(DateDiff("d",{reservation_request.rr_create_stamp},{reservation_request.rr_completestamp})-(if datepart("n",{reservation_request.rr_create_stamp}) < datepart("n",{reservation_request.rr_completestamp})+1 then 0 else 1),DateDiff("n",{reservation_request.rr_create_stamp},{reservation_request.rr_completestamp}) mod 60,00)

 

[DrSimon]

Sorry, I haven't time to look at your calculation in detail but Format([Date1]-[Date2],"d hh:mm") works for me when I'm doing date calculations
Simon Rouse

 

[caddy36]

Thanks

But I'm having trouble finding exactly where in my statement to insert your Line. What exactly do I need to alter? I've tried a few things and nothing has been successful.

Appreciate the help DrSimon

 

[DrSimon]

I really can't get my head round what your equation is doing! If you can explain that I might be able to help
Simon

 

[caddy36]

Basically its just calculating the difference between a Created Timestamp Field and a Completed Timestamp field in HH:MM

The problem, as I stated, is that once the time becomes greater than 24 hours I get an error statement in Crystal that says time must be betwee 0 - 23 hours.

I know what the problem is and what I need to do, just not how to do it with the statement I've got.

 

[DrSimon]

That's what I thought.
Try Format([reservation_request.rr_completestamp]-[reservation_request.rr_create_stamp],"d hh:mm"). It may be as simple as that.
This assumes that the date stamps are real date/time stamps i.e. include the date and time.
Simon Rouse

 

[caddy36]

Getting errors no matter what varation I use on this one. Here's what I have now, but it's NOT right:

Datepart("h",{reservation_request.rr_completestamp}-{reservation_request.rr_create_stamp}),"d hh:mm")

Suggestions are welcomed....!

 

[DrSimon]

That definitely won't work - you've got all the wrong parameters for Datepart to work.
a) have you tried what I suggested?
b) why are there curly { rather than square [ brackets?

 

[caddy36]

([reservation_request.rr_completestamp]-[reservation_request.rr_create_stamp],"d hh:mm")

Yes I've tried it exactly like above and with various Other things mixed with it. It doesn't like the statement.

 

[DrSimon]

You last message misses out 'Format', was that a mistake?

I've copied this from the control source of a form that works using two date fields:

=Format([Date1]-[Date2],"d hh:nn")
(The :nn is automatically converted from :mm so that Access doesn't get confused with months)
Where [Date1]=09/07/04 14:55:00
and [Date2]=07/07/03 11:03:00
and the answer was 2 03:52

Simon Rouse

 

[caddy36]

Yes:

Format([reservation_request.rr_completestamp]-[reservation_request.rr_create_stamp],"d hh:nn")

Using Crystal 8.5

Error message states: "There is an error in the formula, do you want to save it?"

 

[DrSimon]

This is an Access Forms forum

 

[caddy36]

Obviously there in lies the problem!

 

[caddy36]

Thanks Simon: I've posted this in Crystal Forum. Sorry to take up your time, and thanks for the help....

 

[PHV]

Have you tried this ?
(24*Int({reservation_request.rr_completestamp} - {reservation_request.rr_create_stamp}) + Format({reservation_request.rr_completestamp} - {reservation_request.rr_create_stamp},"h")) & Format({reservation_request.rr_completestamp} - {reservation_request.rr_create_stamp},":nn")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244