Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Bad Time Format String error 1

Status
Not open for further replies.

gromboy

IS-IT--Management
Mar 12, 2008
67
GB
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
 
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
if ... or should work. Post your formula if it does not.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
You've got a 'then' followed by commands and then another 'IF'. You can't do that all in one command
Code:
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
Put the last three lines in their own formula field.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
sorry..from where ?

I took out this bit but got errors

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

Steve
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top