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

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!
-- 
Bonnie W. Anderson
Cincinnati, OH
0
Utf
12/14/2007 5:37:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1336 Views

Similar Articles

[PageSpeed] 28

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
..

"Bonnie A" <bonnielynnw@discussions.microsoft.com> wrote in message 
news:76E30FFC-8FF8-4A3E-B2AE-ABA8A4804698@microsoft.com...
> 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!
> -- 
> Bonnie W. Anderson
> Cincinnati, OH 


0
John
12/14/2007 6:05:40 PM
On Fri, 14 Dec 2007 09:37:00 -0800, Bonnie A wrote:

> 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....

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
12/14/2007 6:41:02 PM
Reply:

Similar Artilces:

Some OWA users cant login but some can
We upgraded to Exchange 2003 from 5.5 and rolled out OWA. Some of the users can access the OWA system, But some cant. What should I check? Are the mailboxes all on 2003 yet? Are you still in mixed mode? How long ago did you migrate the mailboxes? I've experienced a similar situation where I needed to wait for either SRS or the ADC to replicate information for migrated mailbox before OWA could be used. This took up to an hour sometimes. Also, did you check the permissions of the user account to make sure they have access to OWA? I am having the same issue. It has been 3 weeks f...

Can I delete OE and just use Microsoft Outlook 2002
I would like to delete Outlook Express is this advisable? I am going to be using Microsoft Outlook 2002 which I recieved w/Toshiba PDA and already have it set up on the same PC as OE. Can I use Microsoft Outlook for multiple e- mail accounts? Differences Between Outlook and Outlook Express http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257824 With Outlook, you can integrate and manage e-mail from multiple e-mail accounts, personal and group calendars, contacts, and tasks. "Delia Marlowe" <anonymous@discussions.microsoft.com> wrote in message news:780901c4025...

How can I use my calendar and other features wo using Outlook emai
I have Windows7 OS. I upgraded to MSO 2007. I cannot access the calendar, etc. without setting up an email profile which my ISP will not allow using the email address. How can I use my calendar and other features without using Outlook email? I could use the calendar on previous versions without setting up an email account. Why can't you use Outlook w/o a mail profile? What happens when you try? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft....

Format text for "mark my comments with..." #2
Is there a way to format the font & colour of the text I may type into the body of an existing email in Outlook 2003? I have switched the option on so that the comments are flagged with my initials but I wanted them more prevalent by being able to format the colour of the text. At the moment its a manual task. With Word as the editor: Open Word, choose Tools | Options | General tab ... Email Options button ... you can choose the Font in when "Replying or Forwarding messages" -- Bill Rodgers MVP "Greg J" <frog66@flashmail.com> wrote in message news:11246865...

Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name, birth date, etc.) for thousands of people. The table is sorted by state. I want to be able to run a macro on that table that will create separate pipe delimited text files for each state with all fields included. Any help would be appreciated? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 Instead of exporting the table, export a query based on the table. You will have to have some code that loops through the states and modifies the quer...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Search for a text in a cell and give the outcome later
Dear expert, C30 to C60 contains some data. Say I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31 It gives me the anwer 20 in D31. 20 is the squence of piano. How can I use the function and use return the the whole cell content which is "Play Piano"? I only know where it is. How to locate where and give the whole content in D32 please? Thanks Elton Play games Play golf Play guitar Play piano Play Kite Play Sccoer Play cards Play WII Play music =INDEX(C30:C60,D31) -- Regards! Stefi „Elton Law” ezt írta: &g...

Can't Change Text direction in office 2007
Hi, i have installed office 2007 ( Pro Plus Edition) on a machine. i have faced a strange thing that i have never seen. the option of changing text direction from right to left or left to right does not exist. it come by default with ltr direction. What's the Problem ? Hi, You need to enable a right-to-left language, such as Hebrew or Arabic. To do this, perform the following steps: 1. Close Word. 2. Click Start, click All Programs, click Microsoft Office, click Microsoft Office Tools, and then click Microsoft Office 2007 Language Settings. 3. In the "Available...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

How can I put a text box in every cell?
Two questions: -How can I put a text box in every cell that I am using WITHOUT having to insert on manually every time? AND - How can I make sure that a text box inserts itself automatically into a new row whenever I add a row? Thanks! One way is to select the row with the text box and copy it to the next row. Copy and paste it several times and then select the rows and paste them as a group. "bmowell" wrote: > Two questions: > > -How can I put a text box in every cell that I am using WITHOUT having to > insert on manually every time? &g...

Button or key to conditionally change text foreground color?
I would like to find some quick method (some sort of visible button or hotkey) for the user of a worksheet to change the text foreground color for certain cells in a block of cells (A1:I9) to some specific color if and only if the cell is blank. I.e. when the user hits this "red" key/button, every empty cell in A1:I9 gets a new foreground text color--red. Nothing would be immediately apparent, as these are empty cells, but as soon as the user starts entering values into these cells, the data would be shown in red... a "make all new data red" button/key. Is this possible?...

WINCE desktop icon text
Hello everyone�� How to make the wince desktop icon text background transparent? it is blue and so ugly when i change the Wallpaper��thank you! Auly(from china) I think that, in order to do that, you'd have to rewrite the shell. You have the source, so it should be possible. Look in the right \PUBLIC\SHELL subdirectory for somewhere that the desktop it drawn. Find where the item names are drawn as text and change the code, or allow a registry entry to control whether your change is active or not. I think that, no matter what ...

Auto Populate a form field
I have two tables with 1:1 relationship. Both have same key field (SS Number). There are two forms, one for each table. When I open Form A there is a button to open Form B. When Form B opens, I want SS Number field to equal the SS Number on the current record in Form A. How do I do this? Thank you in advance. Well, IMO the easiest way would be to make Form B a subform of A, and linking via the common key. Or you could make Form A a tabbed form, and A would be the first tab and B would be the second. Just a couple of random thoughts... "A Bit Frustrated&quo...

Office Outlook
Hi, my office outlook is not working properly, error message : "the procedure entry point GETiums could not be located in the dynamic link library MSDART.DLL " . what should I do?? pls advise. Thanks. Download and install the latest version of MDAC from Microsoft. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "david" <david@discussions.microsoft.com> wrote in message news:2C814B57-C82D-4066-8586-7CFDA8750D55@microsoft.com... > Hi...

group of numbers
How can I make a bunch of numbers being recognized as a group? I've got the following numbers: 1, 2, 3, 4, 5 and 6. I want them to be considered as a group. How can I achieve it? Thank you for your attention --- Message posted from http://www.ExcelForum.com/ You can name the range of cells that contain the numbers. There are instructions here: http://www.contextures.com/xlNames01.html If that's not what you're trying to do, perhaps you could give a bit more detail. PeaceMaker < wrote: > How can I make a bunch of numbers being recognized as a group? > > I&...

Can not empty clipboard #2
I have started getting a strange message in several of my MAC Office products including Word and Excel that says Can Not Empty Clipboard. I also can not copy and paste in any program including Safari. Does anyone have any ideas? Thanks harper6702 Reboot and then run Disk Utility Repair Permissions. The clipboard is a file. I suspect it has overflowed or become corrupted. The reboot may take a long time: during a reboot, Unix cleans out its temporary files, and that may fix the issue... If it doesn't, I have no idea :-) On 16/11/06 2:17 PM, in article 1163647035.202984.70550@m73g2...

Can't save new contacts in Outlook 2007
When I try to save new contacts in Outlook 2007, I get an error message: "Could not complete the operation. One or more parameter values are not valid." It's been suggested that I clear the forms cache but cannot find how to do that in 2007 (recommended link was for 2003 version). Can anyone help with that or is there another possible solution? P.S. I am working off line in Outlook, if that matters. Also, I can revise and save revisions to existing contacts with no problem. HJ Why are you starting a new thread? Suggest following the recommendations in ...

HTML is being converted to text in outlook messages
I have a mixed environment of Exchange 2k3 SP2, and Exchange 5.5 SP4. Currently, the Ex5.5 server is acting as the bridgehead, so all inbound/outbound mail passes through the IMC on that box. My problem is with HTML content within emails. I recieve the messages ok (they are displayed as HTML), but when I forward them, the email loses the HTML content, and displays it in plain text. I have checked the "HTML" box setting in the IMC on the Ex5.5 bridghead server. Is there anywhere else that this needs to be enabled? Any help is appreciated. On Thu, 9 Mar 2006 09:20:31 -080...

Changing text based on criteria in an access report
Hello, I've got a textbox in a report, some records of which may contain text within { }. I need to find some way to format only that text (which may be in the middle of a string) so that it appears underlined and so that the brackets disappear. To illustrate, I posted a 5KB graphic here: http://img219.imageshack.us/img219/3523/jexamplemb2.png Does anybody have any suggestions or ideas? Thanks in advance! Mitch Access 2007 can format the text for you, using standard HTML for the underlining. Use the Replace() function to swap the braces for the HTML tags. Earlier versions of Ac...

How can be detected if an Outlook profile is corrupted ?
Hi, I would like to verify in a program if an Outlook profile is valid or not ( is not corrupted ). Is there a method to do this ? Thanks Valid or corrupted are different things. Valid; account exists Corrupted; errors in mail profile Valid; Test Account button in Account Setup Corrupted; not possible as it can be many many things, you'll know when working with it -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "kistvan" <nospam@nospam.com> wrote in mess...

Can I use Free Excel Viewer 97/2000 in Windows XP?
Microsoft downloads doesn't list the free excel viewer compatible with Windows XP. Is there a free download to view and print excel spreadsheets, that's compatible with Windows XP? Linda The Viewer for Office 97/2000 works fine under Windows XP. Gord Dibben Excel MVP On Fri, 22 Oct 2004 15:11:01 -0700, "Linda" <Linda@discussions.microsoft.com> wrote: >Microsoft downloads doesn't list the free excel viewer compatible with >Windows XP. Is there a free download to view and print excel spreadsheets, >that's compatible with Windows XP? Linda An...

Maximum number of process....Windows
Hi, Is there any limit of number of process or thread that can run at a time in Win9X and win2k/XP OS. Plz reply. regds vinay Well, on the toy systems (Win9x), the process limit is fairly small, probably reasonably on the order of a few hundred, but I've never measured it. The limit is based on the available real memory, the available paging space, the amount of GDI space, the number of windows, etc. (the USER and GDI space is shared system-wide under MS-DOS), and, or course, the sizes of the processes. On real operating systems, you will probably run out of patience before you r...

Rich Text & Sorting
I have a database that I need alphabetical and I want to color code some of the names. I have sorted the database alpahbetically and saved it with a new name. To sort it, I have to use Text format in my first and last name fields. When I have them listed as Rich Text it will not give me the option to sort when I highlight either of those fields. When I go in and change the name fields to Rich Text so that I can color code them, my database reverts back to not being alphabetical. What am I missing or doing wrong? Being able to color code fields is wonderful, so glad they final...

can not save or view attachments
I can seem to be able to save or view attachments the reason is that it greyed out what do i do to ungrey it?? Thanks See http://www.slipstick.com/outlook/esecup/getexe.htm to learn how to get the attachments off. One popular method is Attachment Options from www.slovaktech.com For more information on the security features, see http://www.slipstick.com/outlook/esecup.htm If you use OE, hit Forward on a message and open or save the attachment, then close the message. Or disable it using tools | options | security. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 i...

Can I calculate a field with 2 different filter criteria in one q
Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE "abc" in one query, but I can't seem to figure this out, but, I'm a novice at this. Best I can figure is 2 different queries, then a 3rd query to bring the two together. Thanks -- Jim jimd wrote: >Hi, I have a data source table with a customer name field and various metrics >fields, I'd like to sum metric &...