Sums in Queries or reports 1

[tjisaacs]

I am trying to add to column totals together. Either in the report or query I am getting same result. I have two columns one with a value of (3) and the other a value of (2). When I try to add a column to sum these, I am getting 23 instead of 5. Please advise

Thanks

 

[johpje]

check the type of field these columns are. They're probably textfields. if they only include number it's better you change the type. Else you'll have to convert each time you use the fields as a number.

 

[tjisaacs]

I am using a text box in a report column, but the properties are set to General Number. Is this not the correct way to insert this into a report?

Thanks for your help

 

[johpje]

The textbox should be alright. the thing you should check is the Data Type of these two columns (in Design View). This should be 'Number' not 'Text'. if you make a sum of 2 fields the outcome is different for Text or Numbers:

Text: "The" + " " + "World" -> "The World"
or "2" + "3" -> "23"

Number: 2 + 3 -> 5

if you can't change the type of the field itself, you should change it inside the Select statement with a function. CInt() for integers (no decimals) or CDbl() for Doubles (with decimals).
ex:
SELECT CInt(Number1) + CInt(Number2) FROM Table1

Regards

 

[tjisaacs]

Thanks, I think this is my problem but I still can't get it to work.

Here are the two IIf statements I am trying to combine into one total. I have assigned values 1-3 to the below ranges. Now I want to add these values (1-3) together for a total.

TA Complexity: IIf(([TotalTA]>0 And [TotalTA]<41),&quot;1&quot;,IIf(([TotalTA]>40 And [TotalTA]<61),&quot;2&quot;,IIf(([TotalTA]>61),&quot;3&quot))

PM Complexity: IIf(([TotalPM]>0 And [TotalPM]<41),&quot;1&quot;,IIf(([TotalPM]>40 And [TotalPM]<61),&quot;2&quot;,IIf(([TotalPM]>61),&quot;3&quot))

Thank you

 

[johpje]

If you remove the quotes you should get the correct result.

ex:
IIf(([TotalTA]>61),&quot;3&quot)
becomes
IIf(([TotalTA]>61),3))

regards,
Johpje

 

[tjisaacs]

It worked!!

Thanks

 

[tjisaacs]

Since I have added this last field to my querie, the previous fields will not show up on the report. When I remove this total field they show up fine.

Please help

Thanks - You are awesome!!