Time Differences 1

[alexlc]

Hi,
Our IT dept can't fix this on the database (Lotus Notes 6.5 accessed via ODBC) for me at the moment, & the job is quite urgent, so need a work around for poor data entry.
I'm using this formula

numberVar dur:= datediff("s",{@ShiftEnd},{@ShiftStart});//get the seconds between 2 dates
numberVar Hrs;
NumberVar Min;
NumberVar Sec;
stringVar HHMMSS;

Hrs:= Truncate(Truncate (dur/60)/60);
Min:= Remainder(Truncate(dur/60),60);
Sec:= Remainder(dur,60);

HHMMSS:= totext(Hrs,"00") + ":" + totext(Min,"00") + ":" + totext(Sec,"00");

HHMMSS

And @ShiftEnd & @ShiftStart follow the format of
CDateTime({Shift.ShiftDate}&" "&{Shift.ShiftTo})

Unfortunately, it keeps thorwing up an "Invalid Time Value" error >:-<. Can I do this formula just using the times? The start & end are on the same day.

All help gratefully recieved.

Cheers Alex

 

[IanWaterman]

Do you know why you are getting the error.

You can perhaps use the functions Isdate or istime to test if your datesor times are valid, these return a true/false boolean.

Use to test your formula and if a false is returned print and error message. At least then the report will not fall over.

Ian

 

[Madawc]

Break it into several parts, that will show where the problem is.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I think the syntax should be:

CDateTime({Shift.ShiftDate},{Shift.ShiftTo})

... if {Shift.ShiftDate} is of date datatype and {Shift.ShiftTo} if of time datatype.

-LB

 

[alexlc]

Cheers Ibass, it usually works fine with the space etc, but anything that comes up with less typing is cool by me

Just run it again with those changes & the message says
"Query Engine Error: 'HY000:[Lotus][ODBC Lotus Notes] Invalid time value'

Usually it's poor data entry, grrrr Will try Ian's solution now.

Cheers Alex

 

[lbass]

Alex, you need to find out the datatype of {Shift.ShiftTo}, as the error indicates it is not a time. You could try wrapping it in time, as in:

CDateTime({Shift.ShiftDate},time({Shift.ShiftTo}))

-LB

 

[alexlc]

Hi,

It's a time field - have just discovered that it is falling over because people are entering the same start & end time!!!

Thanks for your help, but I'm giving this up as a bad job before someone come to harm. IT will be showing me how to correct the erro on the database tomorrow.

Cheers Alex

 

[lbass]

That should not cause the error. Please post the exact formula you are using.

-LB

 

[alexlc]

Hi,

It's all in my first post. I think it's something to do with notes - I have had this error message before in another database & have a "fix" in the database for that.

IT should be fixing it for me this morning.

Thanks again.

Cheers Alex