Format Concatenate

[Jennifer]

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Jennifer]

FOUND IT in the module!!

 

[Jennifer]

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

 

[Duane Hookom]

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Jennifer]

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Duane Hookom]

I guess I don't understand what you mean by line up. The function returns
values in a single line as long as the control that displays them is wide
enough. This is much like a basic paragraph.

If you want them to display vertically, you generally need to change the
delimiter to Chr(13) & Chr(10).

--
Duane Hookom
Microsoft Access MVP

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

:

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Jennifer]

I want the data horizontally, but ideally would like for each Division to be
in a separate column. Not sure if I can do this with a split or not at all.

I guess I don't understand what you mean by line up. The function returns
values in a single line as long as the control that displays them is wide
enough. This is much like a basic paragraph.

If you want them to display vertically, you generally need to change the
delimiter to Chr(13) & Chr(10).

--
Duane Hookom
Microsoft Access MVP

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP


:

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

:

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Duane Hookom]

The basic reason for the Concatenate function is to combine the multiple
values in a single expression. I'm not sure what your "split" values would
look like.

Are you looking for results like a crosstab query?
Did you try my suggestion using the Left() function?
If this is for a report, did you try my suggestion regarding a multi-column
subreport?

--
Duane Hookom
Microsoft Access MVP

I want the data horizontally, but ideally would like for each Division to be
in a separate column. Not sure if I can do this with a split or not at all.

I guess I don't understand what you mean by line up. The function returns
values in a single line as long as the control that displays them is wide
enough. This is much like a basic paragraph.

If you want them to display vertically, you generally need to change the
delimiter to Chr(13) & Chr(10).

--
Duane Hookom
Microsoft Access MVP

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!
:

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP


:

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

:

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Jennifer]

Duane... you are way above my knowledge base.

Yes, similar to a crosstab, but when I run the crosstab I have too many
columns for example:

Name Austin Dallas Houston Los Angeles Cincinnati
Brock Austin Los Angeles
Carter Dallas
Clark Houston
Cincinnati

Ideally I want this:

Name Division 1 Division 2
Brock Austin Los Angeles
Carter Dallas
Clark Houston Cincinnati

We have 25 admins that each support 4 divisions. So it would populate up to
Divison 4.

The basic reason for the Concatenate function is to combine the multiple
values in a single expression. I'm not sure what your "split" values would
look like.

Are you looking for results like a crosstab query?
Did you try my suggestion using the Left() function?
If this is for a report, did you try my suggestion regarding a multi-column
subreport?

--
Duane Hookom
Microsoft Access MVP

I want the data horizontally, but ideally would like for each Division to be
in a separate column. Not sure if I can do this with a split or not at all.

I guess I don't understand what you mean by line up. The function returns
values in a single line as long as the control that displays them is wide
enough. This is much like a basic paragraph.

If you want them to display vertically, you generally need to change the
delimiter to Chr(13) & Chr(10).

--
Duane Hookom
Microsoft Access MVP


:

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!
:

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP


:

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

:

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[Duane Hookom]

You should be able to tell me if this is for a report or not. If it is for a
report, use a multiple column subreport. This would require absolutely no
code.

--
Duane Hookom
Microsoft Access MVP

Duane... you are way above my knowledge base.

Yes, similar to a crosstab, but when I run the crosstab I have too many
columns for example:

Name Austin Dallas Houston Los Angeles Cincinnati
Brock Austin Los Angeles
Carter Dallas
Clark Houston
Cincinnati

Ideally I want this:

Name Division 1 Division 2
Brock Austin Los Angeles
Carter Dallas
Clark Houston Cincinnati

We have 25 admins that each support 4 divisions. So it would populate up to
Divison 4.

The basic reason for the Concatenate function is to combine the multiple
values in a single expression. I'm not sure what your "split" values would
look like.

Are you looking for results like a crosstab query?
Did you try my suggestion using the Left() function?
If this is for a report, did you try my suggestion regarding a multi-column
subreport?

--
Duane Hookom
Microsoft Access MVP

I want the data horizontally, but ideally would like for each Division to be
in a separate column. Not sure if I can do this with a split or not at all.

:

I guess I don't understand what you mean by line up. The function returns
values in a single line as long as the control that displays them is wide
enough. This is much like a basic paragraph.

If you want them to display vertically, you generally need to change the
delimiter to Chr(13) & Chr(10).

--
Duane Hookom
Microsoft Access MVP


:

I'm not able to get them to line up. I need the concatenate to go from
vertical to horizontal

This is the data in one:

Avanti Foothill Prestige Spirit

Can I split the data?

Thank you!
:

I think you might want the third returned value from all main records to line
up with each other. If this is the case, you would need to use a fixed space
font and an expression like:
Division: Concatenate("SELECT Left(DivisionCode & ' ',10) FROM
tblDivisions WHERE AdminID =" & [AdminID])

Another alternative would be to use a multi-column subreport in place of the
Concatenate() function.

--
Duane Hookom
Microsoft Access MVP


:

Sorry for all of the questions. I want to set a number of spaces for each
record so when I design my report each row will "line up". Is this possible?
Or to set the concatenate to populate in seperate columns?

:

I fiinally got the concatenate to work and LOVE it. But want to see if there
is a way to format it a different way. I am using:

Division: Concatenate("SELECT DivisionCode FROM tblDivisions WHERE AdminID
=" & [AdminID])

and the records are seperated by a comma. I want to separate by a space.

thanks for all the help!!

 

[CafeenMan]

If you want to count spaces and have columns line up then you have to use a
fixed-width font.

The "proper" way to do it requires a lot of coding. And I mean a lot!

You need to find the textwidth of whatever the string is (what you're
concatenating) as well as which field in each column is the longest so that
you can determine the width of each column (plus space between columns).

If you don't do it that way then you have to wrap lines within columns.

What it comes down to is you have to pre-calculate the entire report and
then format it in code depending on what the calculations gave you.

I wouldn't try it unless you are pretty good with VB or VBA. Best option is
to create the report as has already been suggested.