String is non-numeric error 2

[cindyknapp]

I am new to CR and I've been successful in creating formulas to calculate difference between a start and end time. However, the report bombs with a message "The string is non-numeric".

The original fields that are used in the difference calculation are strings which I convert to number. The string field looks like 08:30.

I convert all string fields to numbers, then
convert to seconds, then
calculate the difference, then
convert seconds back to HH:MM:SS.

I looked at the data and it appears that the field(s) are null.

What do I need to do to keep the report from bombing?

 

[darwin101]

Hi Cindy I am in the same situation, i posted the issue on the formula forum and a memeber 'Rhinok' filled me in on what was going wrong. I was able to get around it by writing a sql query instead of using CR. He explained it well.
here is Rhinok's message:

This normally occurs when you name specific character positions (in Left and Right formulas, for example)and your field isn't static. For example, you'll see 12/31/2001 12:12:12 which is 19 characters in length Vs. 1/31/2001 12:12:12 which is 18 characters in length. If your formula is ToNumber(Left(ToText({TableName.FieldName}),2) for the first date then the return is 12. If your formula hits the second date it attempts to convert '1/' to a number and you get the infamous "The string in non-numeric" error message.

Try converting your Date Time values into String within your Report Options Form (File|Report Options). You should then be able to parse your field correctly because the value will be converted into a static format (MM/DD/YYYY 00:00:00, for example).

 

[cindyknapp]

doesn't work - here is the formula where it get the message.


local numbervar hours;
local numbervar minutes;

//parse out the hours portion of the string and multiply by 3600 to convert to seconds.

hours := tonumber ({case.cas_schsta} [1 to 2]) * 3600 ;

//parse out the minutes portion of the string and multiply by 60 to convert to seconds.

minutes := tonumber ({case.cas_schsta} [4 to 5]) * 60 ;

//add up all the seconds.

hours + minutes

The report seems to fail when it reaches a null in the field case.cas_schsta.

 

[MalcolmW]

If the database field contains a null value, then it is not going to be able to determine the seconds for this. To check for nulls;

local numbervar hours;
local numbervar minutes;
If Not IsNull({case.cas_schsta}) then
(
hours := tonumber ({case.cas_schsta} [1 to 2]) * 3600 ;
minutes := tonumber ({case.cas_schsta} [4 to 5]) * 60 ;
hours + minutes
)
Else 0 ;


This assumes that your time values are padded with zeros for consistency, as you showed in your original example (08:30).
Malcolm

 

[cindyknapp]

thank you both - it's working now! WHEW!