Adding on a form: 5+1 = 51 ????

[Guest]

Hi,

I have a form displaying various fields from a query. I want to use unbound
text boxes to add the data displayed in various of these together. However
access seems to be concatenating rather than summing. I have tried:

=Sum([StarterU18]+[StarterO17])

which refers to two fields in the underlying recordsource, both of which are
being displayed in text boxes on the current form. the control displaying
returns [StarterU18] shows 5, that for [StarterO17 ] returns 1. The
calculated field using the string above shows 51. Whats going on, I want it
to say 6!

Thanks

 

[Marshall Barton]

I have a form displaying various fields from a query. I want to use unbound
text boxes to add the data displayed in various of these together. However
access seems to be concatenating rather than summing. I have tried:

=Sum([StarterU18]+[StarterO17])

which refers to two fields in the underlying recordsource, both of which are
being displayed in text boxes on the current form. the control displaying
returns [StarterU18] shows 5, that for [StarterO17 ] returns 1. The
calculated field using the string above shows 51. Whats going on, I want it
to say 6!

Either the fields in the table are Text fields or you are
doing something in the query that's causing it to convert
the fields to text strings.

You should fix whatever it is, but as a quick and dirty
workaround you can use the CInt, CLng or even Val functions:
Clng([StarterU18]) + CLng([StarterO17])

 

[Guest]

That works great, thanks Marshall! (not sure why though..)

The fields on which the two controls in question are based, should both be
number fields, however by the time they reach the recordsource of the form
they have passed through about 4 queries which most likely is the source of
the problem. I just don't know enough about the process of counting and
aggregating using queries so have ended up with a mountain of queries between
my tables and this form.

Thanks again for your help

I have a form displaying various fields from a query. I want to use unbound
text boxes to add the data displayed in various of these together. However
access seems to be concatenating rather than summing. I have tried:

=Sum([StarterU18]+[StarterO17])

which refers to two fields in the underlying recordsource, both of which are
being displayed in text boxes on the current form. the control displaying
returns [StarterU18] shows 5, that for [StarterO17 ] returns 1. The
calculated field using the string above shows 51. Whats going on, I want it
to say 6!

Either the fields in the table are Text fields or you are
doing something in the query that's causing it to convert
the fields to text strings.

You should fix whatever it is, but as a quick and dirty
workaround you can use the CInt, CLng or even Val functions:
Clng([StarterU18]) + CLng([StarterO17])

 

[Allen Browne]

Access allows you to use:
="eleph" + "ant"
to get elephant.

Sometimes it gets confused about whether it is concatenating text or summing
numbers. Typecasting avoids that confusion, and that's the solution Marshall
gave you.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That works great, thanks Marshall! (not sure why though..)

The fields on which the two controls in question are based, should both be
number fields, however by the time they reach the recordsource of the form
they have passed through about 4 queries which most likely is the source
of
the problem. I just don't know enough about the process of counting and
aggregating using queries so have ended up with a mountain of queries
between
my tables and this form.

Thanks again for your help

I have a form displaying various fields from a query. I want to use
unbound
text boxes to add the data displayed in various of these together.
However
access seems to be concatenating rather than summing. I have tried:

=Sum([StarterU18]+[StarterO17])

which refers to two fields in the underlying recordsource, both of which
are
being displayed in text boxes on the current form. the control
displaying
returns [StarterU18] shows 5, that for [StarterO17 ] returns 1. The
calculated field using the string above shows 51. Whats going on, I
want it
to say 6!

Either the fields in the table are Text fields or you are
doing something in the query that's causing it to convert
the fields to text strings.

You should fix whatever it is, but as a quick and dirty
workaround you can use the CInt, CLng or even Val functions:
Clng([StarterU18]) + CLng([StarterO17])