Hi Team,
ATT an hours field is being entered/saved/shown as a Float.  e.g. 1.5.
The hours can be greater than 99 on some projects.
I want to change the field/display from 1.50 to 01:30, or 1.30 to 1:18. or 123.50 to 123:30.
To facilitate this I added an hours1 field that is type Time.
The Time field doesn't seem to support hours > 99.  I'm probably not surprised.
Or, does it?
To facilitate the change
I have this QnD routine..
{code}
   while not Eof do
   begin
     rHours := FieldByName('Hours').AsFloat;
     fHours := int(rHours);
     fMinutes := frac(rHours) * 60;
     Memo1.Lines.Add(FieldByName('JobNo').AsString);
     Memo1.Lines.Add('fHours = ' + FloatToStr(fHours));
     Memo1.Lines.Add('fMinutes = ' + FloatToStr(fMinutes));
     sHours := 'Time string = ' + FormatFloat('00', fHours) + ':' + FormatFloat('00', fMinutes);
     Memo1.Lines.Add(sHours);
     sHours := FormatFloat('00', fHours) + ':' + FormatFloat('00', fMinutes);
     //
     Edit;
     FieldByName('Hours1').AsDateTime := StrToTime(sHours);
     Post;
     //
     Next;
     //
   end;
{code}
This could probably improved but it is a QnD solution att.

As I said, it works fine until the first 'time' > 99 hours and it tries to save it to the field.

Open to suggestions of alternative ways of handling this, or of making the Time field handle hours > 99.

Regards & TIA,
Ian

Fie on me.  Of course, the Time field won't handle >=24:00.

Ian


1. Correct
2. What are you trying to achieve? Are you wanting to alter the existing data and how its entered or just its presentation? What do you do with it after its entered? Do you add them up or ....


Roy Lambert

Hi Roy,
I want the table field to display & edit as if it were a time field, hours & minutes, but be able to have 100s of hours.
The data may be exported so whatever representation there is in the table needs to be interpret-able to what is displayed so it can be manipulated, say in a spreadsheet or in a report.  Hence my thinking as a decimal, the hours being the whole number and the minutes being the decimal component.  e.g.  2 hours 23 minutes, 2:23 would save as 2.23, 134:47 would save as 134.47.
The display/edit needs to recognise that it can only go to 59 minutes.
Or something like that anyway.

I have been playing with a couple of SpinEdits just to see what I can do but haven't looked at the saving to a table field yet.

Regards,
Ian

Ian


I'd go for 2 integer columns and 1 computed column and a constraint. Something like

HOURS INTEGER DEFAULT 0;
MINUTES INTEGER DEFAULT 0;
DURATION VARCHAR(1) COMPUTED ALWAYS AS CAST(HOURS AS VARCHAR(7))+':'+CAST(MINUTES AS VARCHAR(2));
CONSTRAINT "MinuteValue" CHECK (MINUTES <- 59)
ERROR CODE 42 MESSAGE 'There can be only 59 minutes at most!.'

I'm suggesting splitting because if they're exported to Excel or somesuch there is no datatype I'm aware of that will handle the composite field as a mixture of hours and minutes.

If you make DURATION and actual VARCHAR column then you could use a standard maskedit for editing and a trigger to extract the hour & minute values with the check constraint catching any attempt to enter 75 minutes, or, if you want to be really good, you could convert to hours and minutes regardless of what's entered. If you want to get really clever you can write a custom control.


Roy Lambert

Hi Roy,
All good suggestions  Thank you.
I have found a DB component that I can use nicely.  It allows me to enter hhh:mm and saves it as a Decimal,  hhh.hh.  If the user enter a value for minutes greater than 59 it automatically adjusts the hrs & mins.  

As always, thanks for the advice/assist.
Regards,
Ian

Ian



>I have found a DB component that I can use nicely. It allows me to enter hhh:mm and saves it as a Decimal, hhh.hh. If the user enter a value for minutes greater than 59 it automatically adjusts the hrs & mins.

DO NOT KEEP SECRETS - what component and where did you find it?

Roy

ps

did you like the shouty bit?

Hi Roy,
<VBG>
It is called TESBDBDurationEdit and is one component in a vast array by Glenn Crouch, ESBConsult in his ESBPCS component suite.  https://esbconsult.com/esbpcs/

I have had the suite seemingly since year dot and have forgotten most of what is in it.

The suite, and other stuff he develops is very good.  As an added bonus, he is Australian.

Regards,
Ian

Ian


and even more importantly - he supports Addict

Roy Lambert