Need help with query expression

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a query that - when executed - gives me the following results:

FIELD1 FIELD2 FIELD3
Rec1 X
Rec2
Rec3 Y
Rec4 X Y


The expression below produces the following output:

EXPR: (IIf([FIELD2] Is Null,"","X" + IIf([FIELD2] Is Null,"","Y")))


FIELD1 FIELD2 FIELD3 EXPR
Rec1 X X
Rec2
Rec3 Y
Rec4 X Y XY



QUESTION:
My question is... why is record 3/field3 ("Y") not picked up?


Additional feedback:
Also (later on), I need to write the query in such way that I concatenate
all "existing" values which are separated by a comma. However, the last
value of the
expression string should not be "suffixed" by a comma.

The EXPR should produce some like this:

FIELD1 FIELD2 FIELD3 FIELD 4 EXPR
Miami Richmond Miami, Richmond
San Diego New York San Diego, New York
LA LA



Thanks,
Tom
 
How about using the text concatenation operator "&" instead of the plus operator.

Field2 & Field3

As to why it fails, you've told it to return an empty string if Field2 is null;
so, it does and there it stops.

Question 2 is a bit more complex but

Step1 - get all fields that aren't null and append a comma and a space to the
end of them
(Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ")
This works because when you concatenate a null with a string using the + sign
you get a null; when you concatenate using the & sign then a null gets treated
as if it were an zero-length string.

Step2 - Trim the comma and space off the end, as long as at least one of the
fields has a value

IIF((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ") is
not Null,
Left((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", "),
Len((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ") -2))
 
Hi,



Probably because + propagates NULL. Try &. It is hard to see, with the
non proportional font, what is what, in your tableau.



Hoping it may help,
Vanderghast, Access MVP
 
John,

thanks for the feedback.

Expr: IIf(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ") Is Not Null,Left(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", "),Len(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ")-2)))


Below is the query how it pulls the records (I abbreviated "Field" with "F").

F1 F2 F3 F4
Record #1: 1 2 3
Record #2: 21 22
Record #3: 31 34
Record #4: 100



The result in the expression field are now:

Expr
1, 2, 3,
21, 22,
31, 34
100,

As shown in the example above, the 3rd record is the only record that is displayed properly (no comma at the end) since it is the only record which has a value in the Field4. The 1st, 2nd, and 3rd record are followed by a comma for the last character.

Do you know of a way to drop that "last comma" as well... please keep in mind that maybe there won't be a value for the 2nd or 3rd record either.

I truly would appreciate your help on this. THANKS!


Tom






John Spencer (MVP) said:
How about using the text concatenation operator "&" instead of the plus operator.

Field2 & Field3

As to why it fails, you've told it to return an empty string if Field2 is null;
so, it does and there it stops.

Question 2 is a bit more complex but

Step1 - get all fields that aren't null and append a comma and a space to the
end of them
(Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ")
This works because when you concatenate a null with a string using the + sign
you get a null; when you concatenate using the & sign then a null gets treated
as if it were an zero-length string.

Step2 - Trim the comma and space off the end, as long as at least one of the
fields has a value

IIF((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ") is
not Null,
Left((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", "),
Len((Field1 + ", ") & (Field2 + ", ") & (Field3 +", ") & (Field4 + ", ") -2))


I have a query that - when executed - gives me the following results:

FIELD1 FIELD2 FIELD3
Rec1 X
Rec2
Rec3 Y
Rec4 X Y

The expression below produces the following output:

EXPR: (IIf([FIELD2] Is Null,"","X" + IIf([FIELD2] Is Null,"","Y")))

FIELD1 FIELD2 FIELD3 EXPR
Rec1 X X
Rec2
Rec3 Y
Rec4 X Y XY

QUESTION:
My question is... why is record 3/field3 ("Y") not picked up?

Additional feedback:
Also (later on), I need to write the query in such way that I concatenate
all "existing" values which are separated by a comma. However, the last
value of the
expression string should not be "suffixed" by a comma.

The EXPR should produce some like this:

FIELD1 FIELD2 FIELD3 FIELD 4 EXPR
Miami Richmond Miami, Richmond
San Diego New York San Diego, New York
LA LA

Thanks,
Tom
Click to expand...
Click to expand...
 
Michel:

Thanks, that worked!

--
Thanks,
Tom


Michel Walsh said:
Hi,



Probably because + propagates NULL. Try &. It is hard to see, with the
non proportional font, what is what, in your tableau.



Hoping it may help,
Vanderghast, Access MVP




Tom said:
I have a query that - when executed - gives me the following results:

FIELD1 FIELD2 FIELD3
Rec1 X
Rec2
Rec3 Y
Rec4 X Y


The expression below produces the following output:

EXPR: (IIf([FIELD2] Is Null,"","X" + IIf([FIELD2] Is Null,"","Y")))


FIELD1 FIELD2 FIELD3 EXPR
Rec1 X X
Rec2
Rec3 Y
Rec4 X Y XY



QUESTION:
My question is... why is record 3/field3 ("Y") not picked up?


Additional feedback:
Also (later on), I need to write the query in such way that I concatenate
all "existing" values which are separated by a comma. However, the last
value of the
expression string should not be "suffixed" by a comma.

The EXPR should produce some like this:

FIELD1 FIELD2 FIELD3 FIELD 4 EXPR
Miami Richmond Miami, Richmond
San Diego New York San Diego, New York
LA LA



Thanks,
Tom
Click to expand...
Click to expand...
 
Back
Top