Formula working with Dates/times 1

[Alundil]

I am trying to write a formula to:
1. Calculate the diff between times and display in minutes (but only when the times in question are on the same date)
2. Return a text message when there is no previous time on the day in question.

Fields being calculated:
{V_NI_RECORD.OR_TIME_STOP} using a function to get the previous OR_TIME_STOP with 'Previous({V_NI_RECORD.OR_TIME_STOP})' which I have called {@previous stop time}
{V_NI_RECORD.OR_TIME_START}

Now I have a datediff function (which I have called {@Turn Over}) calculating the difference between OR_STOP_TIME and OR_START_TIME and that works fine except when the previous OR_STOP_TIME is a different date.

I am trying to write a statement in basic syntax and this is what I have so far:
\Dim ORDate As Datetime
ORDate = {V_NI_RECORD.OR_TIME_START}
Dim message As String
If ORDate <= {@previous stop time} Then
message = "No Previous Case"
ElseIf ORDate >= {@previous stop time} Then
formula = {@Turn Over}
End IF

When I use this, the instances where "No Previous Case" should be displayed gives me "0.00" instead

Is there something I am missing?

Thank you

 

[synapsevampire]

You have a fundamental flaw in your operator logic in that you use >= and <=, although that won't make a difference.

I'll use crystal syntax:

datevar ORDate := {V_NI_RECORD.OR_TIME_START};
datevar PreDate := Previous({V_NI_RECORD.OR_TIME_STOP});
stringvar message;
If ORDate <= PreDate Then
message = "No Previous Case"
Else If ORDate > PreDate Then
formula = "Turn Over";
Message

This should be the starting test to determine if @turn over is executing, and when. Since I don't know what's in @Turn over, I won't code for that.

Rather than describing what you've used, and what doesn't work, try supplying a mini spec. with technical information:

Crystal version
Database/connectivity used
Example Data
Expected output

That way people can use the breadth of means available to solve a problem, rather than guessing at what you intend via a description.

-k

 

[Alundil]

synapsevampire, thaks for the response. Does the 'datevar' allow for DateTime fields, or just Dates? Also, the {@Turn Over} is running a 'DateDiff' function calculating the minutes difference between {V_NI_RECORD.OR_TIME_START} and the previous records {V_NI_RECORD.OR_TIME_STOP}.

Crystal ver: 8.5
DB Conn: Oracle 8(believe it is 8.1.7?)/ODBC

ROOM CASE# PROC_DATE OR_START OR_STOP TURN_OVER
OR23 00000 01/01/03 01/01/03 6:00 01/01/03 6:45 -- (this is where the text should be and where I am receiving "0.00" as value)
00001 01/01/03 01/01/03 7:00 01/01/03 8:00 15

 

[tektipdjango]

first you have to check if previous record exist with previousIsNull and then write "No previous case", and only in the other case calculate the turn over

second the datediff calculate the diff in minutes but will not tell you the day has change. You are only sure of this if the turn over is greater than 24 x 60 minutes as there are more than 24 hours. In the other case, you nedd to compare the day part of the date and check if it's different.

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​