Calculating totals

[Inforat]

I created a crosstab query to give me the aggregate totals for each of my 10categories over 3 years. Categories going down, years going across. However when I try to create a report and sum the 3 years I don't get a true sum it adds the fields togethers. Example.

2004 - 0
2005 - 1850
2006 - 0

I get 01850. I need to get a true sum. Do I need to create 3 separate queries to bring the actual values in to do a calculation?

Thanx.

 

[lark_tilly]

Did you use the ampersand instead of the plus sign in your formula?
That would concatenate the field values as strings instead of adding
them up to get a sum.

0 & 1850 & 0 would be 018500
0 + 1850 + 0 would be 1850

 

[neileg]

To add to lark tilly's answer:
If the field you are trying to sum is a text datatype, then 0+1850+0 will also give the answer 018500.

 

[Inforat]

Yep, you are right. I have the field as a text datatype.

 

[neileg]

So the proper answer is to change the datatype to number. A quick fix is to convert the text to numeric using CLng() or Val().