Add Text Field Columns

PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect]))

I have two fields both of which are text.  When I try to add them as above, I get a 
concatenated result, not the sum.  How does one add them?

-- 
 

0
bw
5/18/2007 11:38:49 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
1038 Views

Similar Articles

[PageSpeed] 47

On Fri, 18 May 2007 17:38:49 -0600, bw wrote:

> PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect]))
> 
> I have two fields both of which are text.  When I try to add them as above, I get a 
> concatenated result, not the sum.  How does one add them?

How does one add "This is text." + "this is also text"?
If both fields are Text, then your expression will concatenate them.

One adds numbers. 
Now if the values within the text datatype fields are actually
numbers  (0 - 9) as text, then you can try:

 PFDB:
IIf(Nz(Val([PFDirect]),0)+Nz(Val([PFBulk]),0)=0,"",Nz(Val([PFBulk]),0)+Nz(Val([PFDirect]),0))
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
5/18/2007 11:57:46 PM
Oh, thanks Fredg..
VAL is where it's at.  I should have know this.

"fredg" <fgutkind@example.invalid> wrote in message 
news:1czlk2w4dsdad.7taepx92vgar.dlg@40tude.net...
> On Fri, 18 May 2007 17:38:49 -0600, bw wrote:
>
>> PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect]))
>>
>> I have two fields both of which are text.  When I try to add them as above, I get a
>> concatenated result, not the sum.  How does one add them?
>
> How does one add "This is text." + "this is also text"?
> If both fields are Text, then your expression will concatenate them.
>
> One adds numbers.
> Now if the values within the text datatype fields are actually
> numbers  (0 - 9) as text, then you can try:
>
> PFDB:
> IIf(Nz(Val([PFDirect]),0)+Nz(Val([PFBulk]),0)=0,"",Nz(Val([PFBulk]),0)+Nz(Val([PFDirect]),0))
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail 

0
bw
5/19/2007 12:27:56 AM
Reply:

Similar Artilces:

Format HTML Data As Text
We have a database file (linked by odbc) with a memo field that contains html data from e-mail messages. How can we reformat the data as text without the html tags? ...

Free "PrintCashReceipts" add-in for Great Plains
ICAN Software Corporation has a free add-in for Great Plains that allows users to print cash receipt documents from the cash receipts window. The cash receipt document can be customized easily with Report Writer within Great Plains. It is available on their website http://www.icansoftware.com where you can also get information regarding their eInvestmentAssets product and their receivables enhancements (Adv Cash Receipts & Mass Apply Receivables). Just downloaded this and got a cnk file but no documentation so will not continue. If someone else has the guts to try it first, pleas...

Can I have a CRichEdit not select all text upon WM_SETFOCUS ?
Hy I have an CRichEdit derived class that implements a commands console. I would like the CRichEdit to stop selecting all text when I press Tab (because there is no other control in the dialog but a menu and the CRichEdit) and when some other pop-up window is opend by the parent dialog. Thank you "Timothy Madden" Romania "Timothy Madden" <batman@rmv.spam.home.ro> wrote in message news:3118d8F36ak0sU1@uni-berlin.de... > Hy > > I have an CRichEdit derived class that implements a commands console. > I would like the CRichEdit to stop selecting all text w...

Delete Columns
In Excel, I need a macro that will delete a column based on a cell that will contain a name based on an input box. Thanks, Bernie See you OTHER post. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Bernie" <bml66@aol.com> wrote in message news:1172699736.687460.300590@h3g2000cwc.googlegroups.com... > In Excel, I need a macro that will delete a column based on a cell > that will contain a name based on an input box. > > Thanks, > Bernie > ...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

Pull part of a field
I have a field that gives data like H-A-0012-02 I would like on the report to just display the A from the string. Michelle If the "A" is ALWAYS in the third position, you could use something like: Mid([YourFieldName],3,1) in your query. Regards Jeff Boyce Microsoft Office/Access MVP "Michelle" <Michelle@discussions.microsoft.com> wrote in message news:E4059557-C5DD-4498-AEA7-4200B4172A89@microsoft.com... >I have a field that gives data like H-A-0012-02 I would like on the report >to > just display the A from the string. ...

calculate null text field
Dear Helpers, I would like to creat a calculated field in a query that merch 3 text fields together, like, Index: [field A]&[field B]&[field C]. I don't want to replace the null field with anything so that if [field A] and [field B] are null and only [field C] contains data, the calculated [Index] field will look exactly like [field C]. Is it possible? I hope it is possible coz the Index field is a major field in my database. Thanks a lot to my helpers!! What happens if you type: [field A] & [field B] & [field C] into the Field row in query design, and choose A...

Stored procedure wont insert Fields, RadioButtonLists and CheckBox
Hello Community I have a CheckBox and a RadioButtonList and fields on an aspx web page form. I pass the fields on the form to a method that creates the parameters and executes a stored procedure and the stored procedure is supposed to insert this information as a row into a table. For some reason the stored procedure executes but the row does not get inserted. I can’t see why the row doesn’t get inserted especially since I put BreakPoints in the code all along the way and I see the values just as they get passed into the stored procedure but maybe someone looking at this ...

Remove "don't add space between paragraphs of the same style"?
I'm editing a document that was created by someone else. Somewhere along the way, under format/paragraph options, they clicked the box called "don't add space between paragraphs of the same style." I want to un-click it, but the box is greyed out (showing the checkmark there) and I can't un-click it...and it's screwing up my formatting! Does anyone know how to find this rogue command & remove it? This option is not available for individual paragraphs, only for styles. You must go to the Modify Style dialog to change it. -- Suzanne S. Barnhill M...

add result column to pivot table
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. Add another copy of the Amount field to the pivot table's data area. To display, the fields horizontally, drag the Data button, onto the cell that contains the word 'Total' -- there's a picture here: http://www.contextures.com/xlPivot02.html Right-click on the heading for the second Amount column Choose Field Settings Click the Options button Type a Name for the field, e.g. Percent Choose to 'Show Data as' % of column Cli...

Un-installing COM add-ins
I tried to un-install some COM add-ins from an Excel 2007 user's machine via the Excel dialog to do so. It would not allow the COM add-in to be un-installed. Does anyone know where in the registry the load COM add-in entries are stored so that I can manually delete such entries? Thanks, Bob ...

How keep chart-datapoints displayed when column-width is set to zero
Hello there, my data basis for a chart has one column, that I don't want to be shown in the spreadsheet. However, when I set this column's width to zero, the correspondig data dissapear from the chart. How can I have both, a hidden column and its values shown in the chart at the same time ? Kind regards, H.G. Lamy H.G. - Select the chart, then select Options from the Tools menu, click on the Chart tab, and UNcheck the Plot Visible Cells Only checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ hglamy wrote: &...

increase column number limit?
Is there a way to increase the number of columns beyond ~260? Thanks /b No, sorry. Dan E "bill northlich" <billn@wildpackets.com> wrote in message news:eE3bpxpYDHA.212@TK2MSFTNGP12.phx.gbl... > Is there a way to increase the number of columns beyond ~260? Thanks > /b > 256 is the max. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference -----------...

Outlook 2003 Font Changes When Replying Plain Text
Outlook 2003 is exhibiting a strange behavior when replying to plain text messages. After clicking the "Reply" button, the message appears in the Arial font. In about two to three minutes, the font suddenly changes to Courier which is the correct font. Why is it taking so long for the font to change? Steve Lockridge steve@websitewarehouse.com ...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

Columns to Rows
I'm sure this is a simple problem but I'm trying to convert columns o data into rows of data. What's the easiest way -- Message posted from http://www.ExcelForum.com Hi try the following: - copy your column (CTRL+C) - goto 'Edit - Paste Special) and choose 'Transpose' as action Frank > I'm sure this is a simple problem but I'm trying to convert columns of > data into rows of data. What's the easiest way? > > > --- > Message posted from http://www.ExcelForum.com/ Just read Tom's post, paste special and then transpose -- Messag...

Add-in for Outlook in Japanese
I am looking to download and install add-in for MS project 2007 to Sync/work with outlook in Japanese. Hi Bahram, Maybe this will assist: http://www.experts-exchange.com/Software/Office_Productivity/Project_Management/Microsoft_Project/Q_23539763.html "Bahram" wrote: > I am looking to download and install add-in for MS project 2007 to Sync/work > with outlook in Japanese. > Bahram, I believe you will need to have a japanese version of Project Server in order to receive the add in in Japanese Hope that helps -- Marc Soester [MVP] http://www.i-pm...

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...

pivot table page field hidden items
Is there a vba code that can determine the "pivot table page field hidden items"? I tried the "visible" property for the page field items. It returned "True" only for the "CurrentPage" item and "False" for all the others. You could set each item as the current page. Those that are hidden will create an error, instead of becoming visible. For example: '======================== Sub PageItemsHidden() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim piCurr As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFiel...

Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec
I need a way to fix a print problem. When I go into File - Page Setup, and I choose which rows to repeat at the top ($1:$5) and select which columns to repeat at left ($A:$A), the rows at the top never print completely on pages 2 through the end. Only on the first page can I see the entire title and subtitle. On the remaining pages they get cut off where Column B would begin. Can I fix this? Hi Select only the rows to repeat at the top, and see if that solves your problem -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "unibaby" wrote: > ...

sorting text in 2 columns by likeness
I have 2 columns of text with number values associated with them. Amy 21 Amanda 12 Bill 12 Amy 18 Chris 32 Bill 23 Kathy 8 Chris 12 Kim 15 Curtis 32 Todd 23 Katrhy 21 Todd 5 I need to be able to sort these like this Amanda 12 Amy 21 Amy 18 Bill 12 Bill 23 Chris 32 Chris ...

How do I add multiple comments to a cell?
I need to add more than one comment to data in a single cell, is this possible? Not possible. deberjones wrote: > > I need to add more than one comment to data in a single cell, is this possible? -- Dave Peterson ...

Pivot calculated field
I want to include the right hand column inside the pivot table. It wa calculated externally for this example. The formula is Plan Pct Actual Total. [image: http://www.pbase.com/image/25606965/original.jpg] The formula for the green bordered cell is 49.64% * 3,132,547. I cannot get percent of total in a calculated field, nor can I use th column total in a calculated field. Is this possible? BTW, I cannot get the image to appear inside this note - please clic to see - thanks. TI -- Message posted from http://www.ExcelForum.com I wasn�t able to find away either. What I did was to ...

Retrieving JUST text from a node with child nodes
Hi all, Can't seem to figure out how to retrieve the text of a node with multiple child nodes (and I just want the text of the parent, not the InnerText or InnerXML that comes with it). My doc looks something like this: <parent attrib1 attrib2 attrib3> Extractable string <child> some more text </child> <child> yet more text </child> </parent> What's the best way to return "Extractable string"? You can either just select the text via an XPath expression like this: /parent/text() or, if your XmlD...

counting data in two columns on worksheet that meet certain criteria:
i need to count the number of rows in my 8 column spreadshet that mee one citeria in column c and another in column f? Ex: if column c=3 an column f=no how many rows meet both criteria ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try =SUMPRODUCT((C1:C21=3)*(F1:F21="no")) -- Don Guillett SalesAid Software donaldb@281.com "patam" <patam.xilda@excelforum-nospam.com> wrote in message news:patam.xilda@excelforum-nospam.com... > > i need to c...