Hi. This behaviour arises when for whatever reason the values you are trying to add are actually text strings, not numbers, as the '+' operator acts differently when faced with text strings:
Numeric: 1+1 = 2
String: '1' + '1' = '11'
A dead give-away if you look at the form or table showing your data is that the values will be left-aligned, not right-aligned as numbers usually are.
Access is very good at converting types 'on the fly', and as there is no string equivalent of subtraction or multiplication it converts to numbers when you use the '-' and '*' operators.
You should really fix the problem underlying this - which may well be that the base table from which the numbers are derived has a text field definition instead of a numeric one. Otherwise you will need to convert the strings to numbers before you add them, using a conversion function such as Val, CLng (for whole numbers) or CDbl (for floating-point values).
Example:
- Expression = Val([Forms]![Project P&E]![Man Hours1])+
-
Val([Forms]![Project P&E]![Man Hours2])+
-
Val([Forms]![Project P&E]![Man Hours3])+
-
Val([Forms]![Project P&E]![Man Hours4])+
-
Val([Forms]![Project P&E]![Man Hours5])
All of this can be done without using a macro at all, instead using an unbound text box directly on your form, setting its control source to be
=Val(...) + Val(...) similar to the expression in your macro above. You could then dispense with the Forms![Form Name] qualifiers and simply refer to each control by name within the controlsource expression:
- =Val([Man Hours1])+Val([Man Hours2])+
-
Val([Man Hours3])+Val([Man Hours4])+Val([Man Hours5])
-Stewart
3
