Bad Time Format String error 1

[gromboy]

Hi,
The following formula gives me an error:
numbervar mindiff;
if isnull({PATDRUG.PRESC_TIME})or
trim({PATDRUG.PRESC_TIME}) in [":",""] or
isnull({PATDRUG.START_TIME})or
trim({PATDRUG.START_TIME}) in [":",""] then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.APPT_START})),datetime(currentdate,time({PATDRUG.START_TIME})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,"00")+":"+totext(mins,"00")


The Error appears highlighting this line
time({PATDRUG.APPT_START})

I think it may be due their being no time in this field and the formula outputs ""... ?

Sorry I didnt write the formula so I dont completely understand it

Any ideas ?
Steve

 

[Madawc]

Include an extra test, IsNull({PATDRUG.APPT_START}). And also a test for its format.

I'd also suggest you do a test report that shows the actual values of the fields. Should make things much clearer.

If you have time, it would be better to break the command up into a set of formula fields. Do the same process by stages and get to learn what Crystal can do.

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

 

[gromboy]

Hi Madawc,
I think the error is being caused by the field being null, as on the left hand side in the formula editor against the field {PATDRUG.APPT_START} it says "" and the three variables output 0.

How do I add in the isnull into the formula ? I added it in at the top but I got an error...
Steve

 

[Madawc]

if ... or should work. Post your formula if it does not.

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

 

[gromboy]

Hi Madawc,
This is the revised formula:
numbervar mindiff;
if isnull({PATDRUG.APPT_START})or
trim({PATDRUG.APPT_START}) in [":",""] or
if isnull({PATDRUG.PRESC_TIME})or
trim({PATDRUG.PRESC_TIME}) in [":",""] or
isnull({PATDRUG.START_TIME})or
trim({PATDRUG.START_TIME}) in [":",""] then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.APPT_START})),datetime(currentdate,time({PATDRUG.START_TIME})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,"00")+":"+totext(mins,"00"

I get a "The Keyword then is missing" at the semicolon on this bit
({PATDRUG.START_TIME})));numbervar hrs

Steve

 

[Madawc]

You've got a 'then' followed by commands and then another 'IF'. You can't do that all in one command

trim({PATDRUG.START_TIME}) in [":",""] then
mindiff := 0 [b]else[/b]
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.APPT_START})),datetime(currentdate,time({PATDRUG.START_TIME})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
[b]if[/b] mindiff = 0 then

Break the command up into formula fields rather than trying to use variables.

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

 

[gromboy]

Whats the best way of doing it ?
Sorry, I'm not really a programmer and I inherited this report so its not my work !

Steve

 

[Madawc]

Put the last three lines in their own formula field.

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

 

[gromboy]

sorry..from where ?

I took out this bit but got errors

if mindiff = 0 then
"" else
totext(hrs,"00")+":"+totext(mins,"00"

Steve

 

[lbass]

Try:

numbervar mindiff;
if isnull({PATDRUG.APPT_START})or
trim({PATDRUG.APPT_START}) in [":",""] or
isnull({PATDRUG.PRESC_TIME})or
trim({PATDRUG.PRESC_TIME}) in [":",""] or
isnull({PATDRUG.START_TIME})or
trim({PATDRUG.START_TIME}) in [":",""] then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.APPT_START})),datetime(currentdate,time({PATDRUG.START_TIME})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,"00")+":"+totext(mins,"00"

You had an extra "if".

-LB

 

[gromboy]

thanks LB !
That works a treat
Steve