expressions in a qry

[lwarren1968]

i need to write an expression that combines several groups of fields, however if any of the "last name" fields are empty I do not want to include that group? Thoughts?


=[merchant no 1] & " " & [first name1]& " " & [last name1] & " " & [merchant no 2]& " " & [first name2] & " " [last name2]

 

[Beetle]

What is the structure of the underlying table(s)? Do you actually have fields like;

Merchant, Merchant 2, Merchant 3
First Name 1, First Name 2, First Name 3

etc.?

 

[Pat Hartman]

You would use the + in place of several of the &'s. The & treats null values as ZLS (Zero Length Strings) but the + ignores them.

This should do it but air code is difficult so there may be a syntax error.

=IIf(IsNull([last name1], IIf(IsNull([last name2]),null, [merchant no 2]& " " & [first name2] & " " [last name2]), [merchant no 1] & " " & [first name1] & " " & [last name1] & IIf(IsNull([last name2]), " " & [merchant no 2]& " " & [first name2] & " " [last name2]))

PS, your table is not properly normalized.

 

[lwarren1968]

trying this again. I somewhat figure it out and it worked on my first set of fields, but it did not work on the second set. I figured I'd just break down and then combined all. Although my [Value 2] is blank the expression return all but the [Value 2] field? it should not of returned any data?


Expr2: IIf(([Value 2]) Is Not Null,[,sw_] & " " & [label 2] & " " & [=1] & " " & [value 2])

 

[Pat Hartman]

Perhaps you should post a database with sample data and the query you used assuming you didn't use my suggestion.

People are always willing to help with complex queries but you can't expect us to also create our own test beds. You need to do at least that much and post the query you are using and also post the results you want so we can determine what is wrong with the query.