Concatenation of blank and text fields

  • Thread starter Thread starter Catherine
  • Start date Start date
C

Catherine

Hi everyone,
I hope you can help. I have a query with 5 different
fields of info which look like this:
LessonA, LessonB, LessonC, LessonD, LessonE
5 7 19
2 4

and so on. What I need to do is combine the fields so
they will look like this: 5, 7, 19 or 2, 4 in a report.

I've tried IIf(IsNull) to build an expression but the
result ends up: 5, 7, 19, ,

Help! Is there a another/simple way to do this? Thanks so
much!
 
OutputField: [LessonA] & (", " + [LessonB]) & (", " + [LessonC]) & (", " +
[LessonD]) & (", " + [LessonE])

The above expression takes advantage of the fact that a Null value
propogates through a "+' operation but not through a "&" operation.
 
THANK YOU THANK YOU THANK YOU!! It worked perfectly. I
just have one more question: is there a way to ensure the
#'s display in ascending order or is it on the data entry
side to ensure? Thank you very much once again.
-----Original Message-----
OutputField: [LessonA] & (", " + [LessonB]) & (", " + [LessonC]) & (", " +
[LessonD]) & (", " + [LessonE])

The above expression takes advantage of the fact that a Null value
propogates through a "+' operation but not through a "&" operation.

--
Ken Snell
<MS ACCESS MVP>

Catherine said:
Hi everyone,
I hope you can help. I have a query with 5 different
fields of info which look like this:
LessonA, LessonB, LessonC, LessonD, LessonE
5 7 19
2 4

and so on. What I need to do is combine the fields so
they will look like this: 5, 7, 19 or 2, 4 in a report.

I've tried IIf(IsNull) to build an expression but the
result ends up: 5, 7, 19, ,

Help! Is there a another/simple way to do this? Thanks so
much!
Click to expand...


.
Click to expand...
 
The only way I can think of to do that would be to write a custom VBA function
that would sort the values in order and then concatenate them. Otherwise, it is
up to the data entry folks to put them in order or for you to restructure your
table and then use a custom VBA function to concatenate them
THANK YOU THANK YOU THANK YOU!! It worked perfectly. I
just have one more question: is there a way to ensure the
#'s display in ascending order or is it on the data entry
side to ensure? Thank you very much once again.
-----Original Message-----
OutputField: [LessonA] & (", " + [LessonB]) & (", " + [LessonC]) & (", " +
[LessonD]) & (", " + [LessonE])

The above expression takes advantage of the fact that a Null value
propogates through a "+' operation but not through a "&" operation.

--
Ken Snell
<MS ACCESS MVP>

Catherine said:
Hi everyone,
I hope you can help. I have a query with 5 different
fields of info which look like this:
LessonA, LessonB, LessonC, LessonD, LessonE
5 7 19
2 4

and so on. What I need to do is combine the fields so
they will look like this: 5, 7, 19 or 2, 4 in a report.

I've tried IIf(IsNull) to build an expression but the
result ends up: 5, 7, 19, ,

Help! Is there a another/simple way to do this? Thanks so
much!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top