Numbers are text-need to add them

[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!

 

[Duane Hookom]

Your table structure seems a bit un-normalized. However, you can convert text
to numbers using the Val() function.
ES: Val([Item1]) + Val([Item2]) + Val([Item3]) + Val([Item4]) + Val([Item5])
+ Val([Item6])

 

[Bonnie A]

Duane, you are a GEM!

Thank you for the speed of light repsonse. I had just posted and gone to
the printer. When I returned, you had replied - with a solution!

I'm dealing with a table of data that is provided to me like that each time
and I have to play with it to summarize the data.

Again, I really appreciate your help. I LOVE this newsgroup!
--
Bonnie W. Anderson
Cincinnati, OH

Your table structure seems a bit un-normalized. However, you can convert text
to numbers using the Val() function.
ES: Val([Item1]) + Val([Item2]) + Val([Item3]) + Val([Item4]) + Val([Item5])
+ Val([Item6])

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

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!