Numbers in a text field-can I add them up?

[Bonnie A]

Hi everyone! Using A02 on XP.

I have a table of data with survey response fields that contain a 0,1,2,3,4
or 5. However, the fields are formatted as text, not numbers. I need to add
up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging.
I cannot change the field types from text. Must I append to a new table or
can I do something right in my query?

I've got one field in my query like this: ES:
[Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6]

My result is: 553453 or 554444, etc. I want: 25 or 22, etc.

I would really appreciate any help or advice. Thanks in advance for your
time!

 

[John Spencer]

AS long as you are sure to have a value in each field you can use
CLNG(Item1) or if you might have blanks/null values CLng(Nz(Item1))

Or you can use the function below (it can handle up to 29 fields in a query)

Field: fRowSum(Item1],[Item2],[Item3],[Item4],[Item5],[Item6])

Copy the following function into a VBA module and save (module must have a
name other than fRowSum)

Public Function fRowSum(ParamArray Values()) As Variant
'====================================================================
' Procedure : fRowSum
' Created : 12/6/2006
' Author : John Spencer
' Purpose : Sum a group of numbers passed in,
' handles numbers and text strings that are all numeric
' or that can be interpreted as numbers (1e3 is 1000)
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to nest fRowSum or to add multiple calls
together.
'====================================================================

Dim i As Integer, dSum As Variant

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dSum = dSum + Val(Values(i))
End If
Next i

fRowSum = dSum
End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

Hi everyone! Using A02 on XP.

I have a table of data with survey response fields that contain a 0,1,2,3,4
or 5. However, the fields are formatted as text, not numbers. I need to add
up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging.
I cannot change the field types from text. Must I append to a new table or
can I do something right in my query?

I've got one field in my query like this: ES:
[Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6]

My result is: 553453 or 554444, etc. I want: 25 or 22, etc.

I would really appreciate any help or advice. Thanks in advance for your
time!

ES: Val([Item1])+Val([Item2])+Val([Item3]) + etc....

If there is any chance that one of the fields might be null, then use:
ES: Val(Nz([Item1],0))+Val(Nz([Item2],0))+Val(Nz([Item3],0)) + etc....