Strange concatanation result in form/report

[tezread]

I have a form that when you choose a start and end date and click preview - a report is run. In that report I have a three columns and another one that is supposed to be a 'total'
Problem is the total column is not summing the total it is concatanating the three columns e.g.

column 1 = 3
column 2 = 4
column 3 = 3


total = 343


what on earth is that all about?

 

[gemma-the-husky]

presumably you are adding what happen to be text values

so that 3+4+3 becomes 343

you probably need to coerce them to integers, so that

clng("3")+clng("4")+clng("3") =10

if you have the possibility of nulls, you may need to allow for those in the expression. nz function is your friend in that case

 

[tezread]

Hi

been a while since I followed this up!

I now have

=CLng([0]+[1])

in the control source of the text box on the report but it is still concatanating it instead of coercing it to an integer

 

[missinglinq]

You have to coerce each component into an integer first, then add them together! so

=CLng([0]+[1])

would need to be

=CLng([0])+CLng([1])

Linq ;0)>

 

[vbaInet]

Might be worth adding Nz()

=CLng(Nz([0], 0))+CLng(Nz([1], 0))

Just realised gemma-the-husky had already mentioned this.

By the way, if the datatype of your fields were Number you wouldn't have had this problem.

 

[missinglinq]

As VBAInet said, if the Datatype of your fields were Number you wouldn't have had this problem. The general rule, when dealing with fields composed entirely of digits, is to define them as Numbers if there's any possibility of math being done with them, and as Text if no math is involved.

Linq ;0)>