Results 1 to 10 of 10
  1. #1
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34

    Nz field concatenating

    Hi,



    I'm trying to add two expressions together and am having them concatenate as opposed to summing the numerical values, i.e. 0+0 = 00 instead of 0+0=0. I have an Nz function over both expressions and I've found that if I remove it, they will sum instead of concatenating. Here are the two expressions I'm using. Any idea why this would be happening?

    Code:
    Completion_1: Nz(IIf([status]="pending",Switch([satelliteprovider]="Charter",0.43),0),0)
    and

    Code:
    Completion_2: Nz(IIf([status]="pending",Switch([satelliteprovider]="charter",0.67),0),0)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Nz function results in a string value. EDIT: wrong conclusion, see later post.

    The + is valid arithmetic operator but also will perform concatenation of two string values. If at least one value is number type and other strings have only digits then the operator will sum (presence of alpha will error). The concatenation feature of + is a holdover from ancient BASIC.

    The preferred concatenation operator in Access and VBA is the & character. Use & instead of + if you want to guarantee concatenation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34
    Sorry, to clarify, I have a third field in which I'm trying to add these two fields together and when I do so it concatenates them. The weird thing is that if I even remove the Nz from one of those two expressions they add, but if I have it on both it concatenates even in instances in which the Nz isn't in effect, so when the expression actually returns a specified value as opposed to a null field being converted to a zero. Here is the third expression

    Code:
    test: [completion_1]+[completion_2]

  4. #4
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34
    just reread your reply and saw the first part about nz returning a string. That would definitely do it. Those two expressions are very slimmed down versions of the actual expressions to help me isolate the issue. Basically what I'm trying to do is assign a set of values to field [Completion_1] and a second set of values to field [Completion_2]. Completion_1 will always be null where Completion_2 has a value. Then I want to combine them into 1 field by adding the two fields together. Since I can't add a null to a number, I'm using the Nz function to set the nulls to zero. I don't see how I can get around this without nz.

    I'm doing this because I have so many different variables to assign that I usually run into a character limit or something, hence splitting the expression in two where the values don't duplicate and adding them together

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Sorry, correction to my earlier statement. I just did some testing with Nz in the VBA immediate window and in a textbox. If I tell Nz to return 0, then the return is a number, not string. So that doesn't explain your issue and I am doing some more testing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34
    first off, thanks for the help. Secondly, I did read somewhere that if used in a query expression, which is how I'm using it, Nz will always return a string

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Neither of those expressions will ever result in Null so the Nz is meaningless. With or without Nz I get the same results in immediate window. I tested with combinations of Null and string inputs for both values, with and without Nz, still works.

    I tested Nz expression in query and result is also number, not string.

    I thought I had encountered situation in past where Nz returned string, but not finding it now.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34
    well, I think I found a solution to my problem. Since, at the core, all I really want is for there to be no null values between either of these two fields, I just added a condition to the end of my Switch function that will always be true and assigned it a value of 0. That way anything that satisfies earlier conditions of the switch will be assigned the appropriate value and all others that would've been null due to not satisfying any conditions of the switch are now set to 0. Here are the actual two expressions I'm using

    Code:
    Completion_1: IIf([status]="pending",Switch([satelliteprovider]="Charter",0.43,[satelliteprovider] In ("centlink","BBQWEST"),0.6,[satelliteprovider]="cableone",0.24,[satelliteprovider]="BH",0.27,[satelliteprovider]="bb8",0.22,[satelliteprovider]="bb7",0.68,[satelliteprovider]="bb6",0.33,[satelliteprovider]="bb4",0.55,[satelliteprovider]="bb2",0.42,[satelliteprovider]="bb",0.44,[satelliteprovider]="mediacom",0.29,[satelliteprovider] Is Not Null,0),Switch([status] In ("chargeback","cancelled","not scheduled"),0,[status]="completed",1))
    Code:
    Completion_2: Nz(IIf([status]="pending",Switch([satelliteprovider]="wildblue",0.67,[satelliteprovider] In ("direcway","hughesnetf"),0.72,[satelliteprovider]="dish" And [promotion]<>"dishnet" Or [promotion] Is Null,0.85,[satelliteprovider]="dish" And [promotion] Not Like "*dishnet*",0.73,[satelliteprovider]="tw",0.18,[satelliteprovider]="suddenlink",0.24,[satelliteprovider]="Phone3",0.2,[satelliteprovider]="other2",0.29,[satelliteprovider]="insight",0.12,[satelliteprovider]="Frontier",0.6,[satelliteprovider]="el",0.3,[satelliteprovider]="Cox",0.34,[satelliteprovider]="clearwire",0.4),0),0)
    Code:
    Completion: [Completion_1]+[Completion_2]

  9. #9
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34
    They actually will result in a null because the [satelliteprovider] field has many more values aside from "charter", so every record where [status]="pending" and [satelliteprovider]<>"charter" gets a null. This happens because the condition of the iif is satisfied so the else,0 doesn't come into play and the condition of the switch is also not satisfied. This results in a null value

    That's weird that you're getting a number. I had always thought Nz returned a number but that seems not to be the case here

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Ahh, I did not test other string values. Glad you figured out a solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-30-2012, 10:50 AM
  2. Concatenating rows & records
    By Alex Motilal in forum Queries
    Replies: 2
    Last Post: 08-24-2011, 12:01 PM
  3. Concatenating and then exporting to excel
    By canfish in forum Queries
    Replies: 6
    Last Post: 08-18-2010, 09:52 AM
  4. Concatenating 3 Felds
    By Dody in forum Queries
    Replies: 2
    Last Post: 02-19-2010, 02:38 PM
  5. Concatenating many fields into one field
    By olidav911 in forum Programming
    Replies: 1
    Last Post: 08-13-2009, 05:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums