Line up text

[petdominic]

I have a column, Members, where sometimes there can be multiple names. I
pull a query and then run a report. However, the data entered is all on one
line but I want the report to have it appear as a list. For example, the
data in the table appears as Jane Doe - Spring 07 and John Doe - Spring 08.
How do I get it to appear as follows in my report:

Jane Doe - Spring 07
John Doe - Spring 08

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[Guest]

If all have a dash where you want to align on then you can use a calculation.
Field_Title: Left(Left([YourField], Instr([YourField], "-") & "
"), 33) & Right([YourField], RevInstr([YourField], "-"))

The first part (Left) pulls all to the left of the dash, pads spaces, and
then limits to 33 (you might need to adjust). The second part (right) pulls
all from the dash to the right.

 

[Guest]

P.S. You will need to use a font like Courier New so that all character
occupy the same horizontal spacing.
--
KARL DEWEY
Build a little - Test a little

If all have a dash where you want to align on then you can use a calculation.
Field_Title: Left(Left([YourField], Instr([YourField], "-") & "
"), 33) & Right([YourField], RevInstr([YourField], "-"))

The first part (Left) pulls all to the left of the dash, pads spaces, and
then limits to 33 (you might need to adjust). The second part (right) pulls
all from the dash to the right.
--
KARL DEWEY
Build a little - Test a little

I have a column, Members, where sometimes there can be multiple names. I
pull a query and then run a report. However, the data entered is all on one
line but I want the report to have it appear as a list. For example, the
data in the table appears as Jane Doe - Spring 07 and John Doe - Spring 08.
How do I get it to appear as follows in my report:

Jane Doe - Spring 07
John Doe - Spring 08

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[petdominic]

Okay, I'm assming I build an expression in the query. Again, I have basic
skills. So this is what I did in Critieria on the query:

Left(Left([Roundtable Member(s)], Instr([Roundtable Member(s)], "-") & "),99)
& Right([Roundtable Member(s)], RevInstr( [Roundtable Member(s)], "-"))

I get the following error message "The expression you entered has an invalid
string. A string can be up to 2048 characters long, including opening and
closing of quotation marks."

Can you please help me troubleshoot? Thanks.

If all have a dash where you want to align on then you can use a calculation.
Field_Title: Left(Left([YourField], Instr([YourField], "-") & "
"), 33) & Right([YourField], RevInstr([YourField], "-"))

The first part (Left) pulls all to the left of the dash, pads spaces, and
then limits to 33 (you might need to adjust). The second part (right) pulls
all from the dash to the right.

I have a column, Members, where sometimes there can be multiple names. I
pull a query and then run a report. However, the data entered is all on one

[quoted text clipped - 7 lines]

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[Guest]

Your assumption is wrong. What I posted is a calculated field in lieu of
your normal field.
Use the Courier New font in the report.
--
KARL DEWEY
Build a little - Test a little

Okay, I'm assming I build an expression in the query. Again, I have basic
skills. So this is what I did in Critieria on the query:

Left(Left([Roundtable Member(s)], Instr([Roundtable Member(s)], "-") & "),99)
& Right([Roundtable Member(s)], RevInstr( [Roundtable Member(s)], "-"))

I get the following error message "The expression you entered has an invalid
string. A string can be up to 2048 characters long, including opening and
closing of quotation marks."

Can you please help me troubleshoot? Thanks.

If all have a dash where you want to align on then you can use a calculation.
Field_Title: Left(Left([YourField], Instr([YourField], "-") & "
"), 33) & Right([YourField], RevInstr([YourField], "-"))

The first part (Left) pulls all to the left of the dash, pads spaces, and
then limits to 33 (you might need to adjust). The second part (right) pulls
all from the dash to the right.

I have a column, Members, where sometimes there can be multiple names. I
pull a query and then run a report. However, the data entered is all on one

[quoted text clipped - 7 lines]

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[petdominic]

My apologies, and don't feel obligated to respond, but you state a calculated
field. Where do I put this? In the report Detail section under Format,
Expression builder? I tried that, along with setting the font to Courier New,
and I can't get it to work. Maybe I'm missing something, but I don't want to
give up! I don't mind doing further reading on it, just unsure where to find
material that can explain how to go about what I'm wanting.

Your assumption is wrong. What I posted is a calculated field in lieu of
your normal field.
Use the Courier New font in the report.

Okay, I'm assming I build an expression in the query. Again, I have basic
skills. So this is what I did in Critieria on the query:

[quoted text clipped - 20 lines]

 

[Guest]

Where do I put this? In the report Detail section under Format,
Expression builder? I tried that, along with setting the font to Courier
New, and I can't get it to work.
What happened? What did not happen?

I see a couple of things. You did not put the padding spaces like & "
"), in the expression. Also do you really need 99 charachers for the
name?
The expression is one line with no wrap to be put in the text box Control
Source that is in the detail section.
=Left(Left([Roundtable Member(s)], Instr([Roundtable Member(s)], "-") & "
"),25) & Right([Roundtable Member(s)], RevInstr([Roundtable
Member(s)], "-"))

--
KARL DEWEY
Build a little - Test a little

My apologies, and don't feel obligated to respond, but you state a calculated
field. Where do I put this? In the report Detail section under Format,
Expression builder? I tried that, along with setting the font to Courier New,
and I can't get it to work. Maybe I'm missing something, but I don't want to
give up! I don't mind doing further reading on it, just unsure where to find
material that can explain how to go about what I'm wanting.

Your assumption is wrong. What I posted is a calculated field in lieu of
your normal field.
Use the Courier New font in the report.

Okay, I'm assming I build an expression in the query. Again, I have basic
skills. So this is what I did in Critieria on the query:

[quoted text clipped - 20 lines]

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[petdominic]

So I selected the text box for Roundtable Member(s) in detail section and
went to Data and Control Source (correct so far I hope). I put in the below

=Left(Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"-") & "
"),25) & Right([Roundtable Member(s)],InStrRev([Roundtable Member(s)],
"-"))

I think it's InStrRev b/c I get a pop up to enter a value when leaving it as
RevInStr.

When I try it the way I plugged it in, I get #Error populated on my report.

I also notice a little error by the textbox and when I click on it, it reads
Invalid Control Property: Control Source and Circular Reference.

I truly appreciate your patience. I'm truly can just get by in Access but
know there is an easier way, thus why I'm being persistent at finding it.

Expression builder? I tried that, along with setting the font to Courier
New, and I can't get it to work.
What happened? What did not happen?

I see a couple of things. You did not put the padding spaces like & "
"), in the expression. Also do you really need 99 charachers for the
name?
The expression is one line with no wrap to be put in the text box Control
Source that is in the detail section.
=Left(Left([Roundtable Member(s)], Instr([Roundtable Member(s)], "-") & "
"),25) & Right([Roundtable Member(s)], RevInstr([Roundtable
Member(s)], "-"))

My apologies, and don't feel obligated to respond, but you state a calculated
field. Where do I put this? In the report Detail section under Format,

[quoted text clipped - 11 lines]

 

[Guest]

I just ried my InstrRev and it is working like Instr so there a work around.
=Left(Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"-") & "
"),25) & Right([Roundtable Member(s)], Len([Roundtable Member(s)])
-InStr([Roundtable Member(s)],"-"))

--
KARL DEWEY
Build a little - Test a little

So I selected the text box for Roundtable Member(s) in detail section and
went to Data and Control Source (correct so far I hope). I put in the below

=Left(Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"-") & "
"),25) & Right([Roundtable Member(s)],InStrRev([Roundtable Member(s)],
"-"))

I think it's InStrRev b/c I get a pop up to enter a value when leaving it as
RevInStr.

When I try it the way I plugged it in, I get #Error populated on my report.

I also notice a little error by the textbox and when I click on it, it reads
Invalid Control Property: Control Source and Circular Reference.

I truly appreciate your patience. I'm truly can just get by in Access but
know there is an easier way, thus why I'm being persistent at finding it.

Where do I put this? In the report Detail section under Format,

Expression builder? I tried that, along with setting the font to Courier
New, and I can't get it to work.
What happened? What did not happen?

I see a couple of things. You did not put the padding spaces like & "
"), in the expression. Also do you really need 99 charachers for the
name?
The expression is one line with no wrap to be put in the text box Control
Source that is in the detail section.
=Left(Left([Roundtable Member(s)], Instr([Roundtable Member(s)], "-") & "
"),25) & Right([Roundtable Member(s)], RevInstr([Roundtable
Member(s)], "-"))

My apologies, and don't feel obligated to respond, but you state a calculated
field. Where do I put this? In the report Detail section under Format,

[quoted text clipped - 11 lines]

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[raskew via AccessMonster.com]

Hi -
Example from debug (intermediate) window:

x = "Jane Doe - Spring 07 and John Doe - Spring 08"
y = trim(left(x, instr(x, "and")-1))
? y
Jane Doe - Spring 07
z = trim(mid(x, instr(x, "and")+3))
? z
John Doe - Spring 08

I just ried my InstrRev and it is working like Instr so there a work around.
=Left(Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"-") & "
"),25) & Right([Roundtable Member(s)], Len([Roundtable Member(s)])
-InStr([Roundtable Member(s)],"-"))

So I selected the text box for Roundtable Member(s) in detail section and
went to Data and Control Source (correct so far I hope). I put in the below

[quoted text clipped - 33 lines]

 

[Guest]

I missed the boat at the begining.
=Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"and")-1) &
Chr(13) & Chr(10) & Right([Roundtable Member(s)], Len([Roundtable
Member(s)])-InStr([Roundtable Member(s)],"and"))


--
KARL DEWEY
Build a little - Test a little

Hi -
Example from debug (intermediate) window:

x = "Jane Doe - Spring 07 and John Doe - Spring 08"
y = trim(left(x, instr(x, "and")-1))
? y
Jane Doe - Spring 07
z = trim(mid(x, instr(x, "and")+3))
? z
John Doe - Spring 08

I just ried my InstrRev and it is working like Instr so there a work around.
=Left(Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"-") & "
"),25) & Right([Roundtable Member(s)], Len([Roundtable Member(s)])
-InStr([Roundtable Member(s)],"-"))

So I selected the text box for Roundtable Member(s) in detail section and
went to Data and Control Source (correct so far I hope). I put in the below

[quoted text clipped - 33 lines]

My Access skills are basic so the more detailed the better. Thank you in
advance.

 

[raskew via AccessMonster.com]

Karl -

Jane Doe - Spring 07
nd John Doe - Spring 08

eh?

Suspect you'll want to use calculated fields to capture the multiple data.

Bob

I missed the boat at the begining.
=Left([Roundtable Member(s)],InStr([Roundtable Member(s)],"and")-1) &
Chr(13) & Chr(10) & Right([Roundtable Member(s)], Len([Roundtable
Member(s)])-InStr([Roundtable Member(s)],"and"))

Hi -
Example from debug (intermediate) window:

[quoted text clipped - 17 lines]