Numbers are text-need to add them

B

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

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])
 
B

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


Duane Hookom said:
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


Bonnie A said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top