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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal Reports 11.5

Status
Not open for further replies.

socalgm

Programmer
Joined
Oct 17, 2012
Messages
5
Location
US
I am trying to convert a varchar field that data stored looks like time (02:30:00). i need to convert and then sum the values. Tried using the Val({ContHist.DURATION}) it will sum only the hours and not the Minutes.
Help please
 
The following will give you the time in seconds:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),Time({table.varchar})))

You can then place this in the detail section and right click and insert a sum on it.

-LB
 
Thanks LB, but this yields a "0" value. the issue is the field is a varchar type. The data looks like time (01:30:00). I am trying to extract the value of "1.5", so that I can SUM all values in order to report correctly. Currently I can get a value of "1.00", missing the 1/2 hour values. I inserted you suggestion, but it yields me only a 0.00 value...... Lost!

 
I understand what you are looking for and the formula should have returned the number of seconds. Please do two things:
1-Confirm that the field is a string by browsing the field to see the data type.
2-Post the exact formula you tried.

-LB
 
Yes, the field is a VARCHAR(14) and it does show up in crystal as such.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60

 
Ok, now try MY formula, exactly as posted without using your formula conversion. Just plug in {ContHist.DURATION} for {table.varchar}.

-LB
 
I did as you said and get a "Bad rime format string" error when trying to save the formula. I assume that I have some data in the duration field that is not formatted like I expected?
 
Please show examples of the various ways the field displays.

-LB
 
In the attached .png file, note that the column under the total hours field is like 14:30:00 and it then converts to 14.00 hours, the daily hours are just a SUM of column at the left and getting no minutes. Thank you for you help.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60


Capture_aospu4.png
 
I am asking to see what the duration field looks like without any formula conversions. It looks from your formula that it sometimes displays like:
4:30:00, for example.

-LB
 
1 - Can the duration field be null?

2 - Please post MY formula exactly as you implemented it, by copying it into this thread, so I can troubleshoot it. It should look like my post on 2 Nov 17 20:13.

-LB


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top