Nz field concatenating

[drewetzel]

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?

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

and

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

[June7]

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.

[drewetzel]

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

test: [completion_1]+[completion_2]

[drewetzel]

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

[June7]

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.

[drewetzel]

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

[June7]

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.

[drewetzel]

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

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

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)

Completion: [Completion_1]+[Completion_2]

[drewetzel]

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

[June7]

Ahh, I did not test other string values. Glad you figured out a solution.