Line up text

P

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

KARL DEWEY

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

KARL DEWEY

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


KARL DEWEY said:
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


petdominic said:
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.
 
P

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.



KARL said:
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.
 
K

KARL DEWEY

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


petdominic said:
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.



KARL said:
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.
 
P

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.

KARL said:
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]
 
K

KARL DEWEY

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


petdominic said:
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.

KARL said:
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.
 
P

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.


KARL said:
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]
 
K

KARL DEWEY

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


petdominic said:
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.


KARL said:
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.
 
R

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


KARL said:
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]
 
K

KARL DEWEY

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


raskew via AccessMonster.com said:
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


KARL said:
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.
 
R

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

KARL said:
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]
 

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