Problem with Macros

[jpottorff]

I have a macro setup to add up the hours each person spends on a project and display that on the form.

Action = SetValue
Item = [Forms]![Project P&E]![Total Man Hours]
Experssion = [Forms]![Project P&E]![Man Hours1]+[Forms]![Project P&E]![Man Hours2]+[Forms]![Project P&E]![Man Hours3]+[Forms]![Project P&E]![Man Hours4]+[Forms]![Project P&E]![Man Hours5]

Instead of it adding the values up it puts each in the block (i.e. if each person spent 1 hour the results displayed shows up 11111). When I try to subtract, multiply or divide it works fine. I'm not sure if it matters but I am using access 2003.

Thanks,
James

[Stewart Ross]

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

[jpottorff]

Thank you. I didn't realize that having the properties set to text instead of number would matter.