How to sum a text field of table ?

[sayfrndship]

Hi,

I have following query. The field Member_price and nonmember_price is of text datatype. I need to sum up these field, Upto fields to be integer datatype. The following query without CInt funciton works alright. But I need to sum that field having text datatype . Tell me how to do ?

sql = "Select Sum(CInt(Member_price)),Sum(CInt(nonMember_price)),count(Member_id) from event_history where Event_id=" & c034_id

[stephan.gerlach]

what about this one



sql = "Select Sum(CInt(Member_price)) AS member_price,Sum(CInt(nonMember_price)) AS non_member_price,count(Member_id) AS member_id from event_history where Event_id=" & c034_id

[bluestartech]

it would be best to use an int, or currency datatype in your table..alternatively pull the rows out and do the math in your script/program

[sayfrndship]

I need to have it text, not int type, I have been inserted an entry $75.00, by removing $. there is now 75.00, tell me
1- Is sum applicable to float number ?
2- If dollor is included in table data, how to get that value and remove $ from value and get Integer to add ?

[mataichi]

#1 - I think Sum returns whatever type of number you give it, if it is an int it will return an int, if float it will return a float

#2 - You can use substring function to remove the '$'... ( http://msdn.microsoft.com/en-us/library/ms187748.aspx )

[sayfrndship]

stephan.gerlach it's not workign

[sayfrndship]

Ok's i got change my field data type from Text to Currency ! it's now working. I have changed this on local site and not on remote site. On remote site it's still text. But now i m not getting that error. It's working alright too. i m surprized why it is happening ???? i just uploaded my asp file to remote site with following query !

Select Sum(Member_price),Sum(NonMember_price),count(Member_id) from event_history where Event_id=" & c034_id