Inoperative ADDITION (+) function in Access2000 1

[Waxaholic]

I cannot seem to get the addition(+) function in access 2000 to work. I am trying to calculate two columns but all i get is a concatenation of data. For example, column 1 cell 1 contains 204 for data, and column 2 cell 1 contains 5 for data. The returned value is always 2045 instead of 209. If i change the argument to SUBTRACTION, everything is fine. I thought "&" was used for concatenation. Anybody have a solution for a newbie to access?

Thanx,

Wax

 

[dneufarth]

I believe '+' is interpreted as concatenate if field data-type in defined as other than a numeric format. Check your data-types.


Dave

 

[Waxaholic]

Where exactly would i look to verify field data types?

Wax

 

[dneufarth]

Design view of the table containing the field.

Dave

 

[Waxaholic]

Got it to work. Had to go to the source of the data and modify formatting. What a PITA. I have about 40 tables i have to go back and change from "text" to "number" formatting. Thanks for the guidance.

Wax

 

[MichaelRed]

Sorry, this is in the form of better late than never, but ........

You can coerce the field(s) to numeric for the calculation by use ot the Val() or the family of Cxxx() (CLng, CSng, CInt. ...) functions.

Depending on the overall app needs, you may be better off changing the table field types, but in some situations the coersion of the text to the value at run time is actually better.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Waxaholic]

MichaelRed,

Would it be possible to coerce fields in a linked table? I know i cannot set the format properties for a linked table. Apparently the linked table draws it's formatting from the source data. Yet, there has to be a way of forcing incoming/linked data to format the way i want without doing it at the source itself. The source is out of the question because the data is updated there once a day and overwrites any formatting in place at the time. Perhaps the use of the Val() or Cxxx() you mentioned above? If so, could you tell me where i would input these arguments, i.e. Table level, Query level. Thanks for your help.

Wax

 

[MichaelRed]

You can place the coercision functions at any reference to the variable (or Field) reference. The specific placement obviously depends on your app, however as a GENERAL rule, the earlier in the process you apply the coercision, the better. In most cases I would apply them at the 'query' level, as it lets me do it only once per recordset retrievial. On the other hand, to do this - and not 'hose' the process, you need to apply some error trapping (use of IsNumeric, Nz, ... ) in the query - to be assured that the query itself doesn't blow up. In a heavy traffic environment, this can become a severe penalty. Another disadvantage of the initial query placement is that you need to privide an alais for each field where you use the coercisions. This, in turn, requires that you either change all of your references to the fields, or use another query to re-alais the field names back to their original names.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[scking]

You need to consider this. If you keep these fields as text then you will also need to place error handling code in to handle the condition of anyone accidently entering any text value which can not be converted to a numeric value. Users are famous for doing something the developer has not considered but this is a case where the developer MUST consider. For instance: what numeric value would result where the user places an 'o' rather than a zero '0'. There result would be an Access error. Even though the easier course may be to cast, or coerce, the value, in my opinion, the better design would be to change the data type in the table design.

Steve King