Combining Fields Within Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Query based off of another Query.

There are a couple dozen fields.

Ten of the Fields are Text fields.

In the New Query, based off of the original Query, I would like to combine
ten text fields into one field. The Text is fairly short in each of the
fields ... exmaple one field is Management another is Support Group and
another is Sales. These fields are populated by Checkboxes on a Form so they
are not free form.

I hope this makes sense.
 
OK, this seemed too easy to work but I put this into my Query and it seemed
to work ...

NEWQUERYFIELDNAME: [fieldname1] & " " & [fieldname2] & " " & [fieldname3] &
" " & [fieldname4] & " " & [fieldname5] & " " & [fieldname6] & " " &
[fieldname7] & " " & [fieldname8] & " " & [fieldname9] & " " & [fieldname10]

The only issue is the spacing seems to be thrown off because if one of the
fields is not selected then there are just spaces but I think I can live with
that. Maybe I'll try and tackle that another day unless someone has a quick
and simple solution. Otherwise I may just go this route for now.
 
If by "not selected" you mean the field has no value, then you could try

NEWQUERYFIELDNAME: [fieldname1] & (" " + [fieldname2]) & (" " +
[fieldname3]) &
(" " + [fieldname4]) & (" " + [fieldname5]) & (" " + [fieldname6]) & (" "
+
[fieldname7]) & (" " + [fieldname8]) & (" " + [fieldname9]) & (" " +
[fieldname10])

This takes advantage of the way that Access uses the two concatenation
operators when working with null values.
"x" & Null = "x"
"X" + Null = Null

You can also use

[fieldname1] & IIF(Len([fieldname2]&"" >0, " ","") & FieldName2 & ...

FrankM said:
OK, this seemed too easy to work but I put this into my Query and it
seemed
to work ...

NEWQUERYFIELDNAME: [fieldname1] & " " & [fieldname2] & " " & [fieldname3]
&
" " & [fieldname4] & " " & [fieldname5] & " " & [fieldname6] & " " &
[fieldname7] & " " & [fieldname8] & " " & [fieldname9] & " " &
[fieldname10]

The only issue is the spacing seems to be thrown off because if one of the
fields is not selected then there are just spaces but I think I can live
with
that. Maybe I'll try and tackle that another day unless someone has a
quick
and simple solution. Otherwise I may just go this route for now.




FrankM said:
I have a Query based off of another Query.

There are a couple dozen fields.

Ten of the Fields are Text fields.

In the New Query, based off of the original Query, I would like to
combine
ten text fields into one field. The Text is fairly short in each of the
fields ... exmaple one field is Management another is Support Group and
another is Sales. These fields are populated by Checkboxes on a Form so
they
are not free form.

I hope this makes sense.
Click to expand...
Click to expand...
 
Thank you, John ... that was pure brilliance and worked perfectly.


John Spencer said:
If by "not selected" you mean the field has no value, then you could try

NEWQUERYFIELDNAME: [fieldname1] & (" " + [fieldname2]) & (" " +
[fieldname3]) &
(" " + [fieldname4]) & (" " + [fieldname5]) & (" " + [fieldname6]) & (" "
+
[fieldname7]) & (" " + [fieldname8]) & (" " + [fieldname9]) & (" " +
[fieldname10])

This takes advantage of the way that Access uses the two concatenation
operators when working with null values.
"x" & Null = "x"
"X" + Null = Null

You can also use

[fieldname1] & IIF(Len([fieldname2]&"" >0, " ","") & FieldName2 & ...

FrankM said:
OK, this seemed too easy to work but I put this into my Query and it
seemed
to work ...

NEWQUERYFIELDNAME: [fieldname1] & " " & [fieldname2] & " " & [fieldname3]
&
" " & [fieldname4] & " " & [fieldname5] & " " & [fieldname6] & " " &
[fieldname7] & " " & [fieldname8] & " " & [fieldname9] & " " &
[fieldname10]

The only issue is the spacing seems to be thrown off because if one of the
fields is not selected then there are just spaces but I think I can live
with
that. Maybe I'll try and tackle that another day unless someone has a
quick
and simple solution. Otherwise I may just go this route for now.




FrankM said:
I have a Query based off of another Query.

There are a couple dozen fields.

Ten of the Fields are Text fields.

In the New Query, based off of the original Query, I would like to
combine
ten text fields into one field. The Text is fairly short in each of the
fields ... exmaple one field is Management another is Support Group and
another is Sales. These fields are populated by Checkboxes on a Form so
they
are not free form.

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top