Concatenation of blank and text fields

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!
 
K

Ken Snell

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.
 
C

Catherine

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!


.
 
J

John Spencer (MVP)

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!


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top