Beginner Formatting question

[Norm]

This is a beginner question on Access forms.

It is also a question being asked by me as the intermediary who doesn't
know or use Access but I'm trying to help in this project.

The question/problem is:

Trying to format a form where we'd like to have:

a data element (left justified to a stop) then leading dots and then a
data element (right justified to a stop) with the number of dots being
determined by Access depending on the length of the data element.

The project is a directory with addresses on the left and telephone
numbers on the right with dots/periods in between. But the number/length
of dots will vary based on the length of the address and phone number
for each person.

Thank you for any tips. Appreciate.

 

[Rick B]

You would need to use an unbound text box for the first item. Something
like...

=[Address] & "..........................."

Make this field as wide as needed.

Then just put your phone number field to the right of it.

 

[Norm]

You would need to use an unbound text box for the first item. Something
like...

=[Address] & "..........................."

Make this field as wide as needed.

Then just put your phone number field to the right of it.

OP back. Thank you.

Will the number of dots expand and contract to fit the space?

And can one set the position of the leading data field (left justify)
and ending data field (right justify)?

Appreciate the help.

 

[Rick B]

Just make sure there are enough dots to fill the space. They will only
print as many as will fit in the field. Just make sure the field is as wide
as you want. Then make sure the phone field is butted up against it. This
would be the same as typing something in the first column of excel that is
too wide, then entering something in the second column. The first column
would only show as many characters as would fit.


--
Rick B

You would need to use an unbound text box for the first item. Something
like...

=[Address] & "..........................."

Make this field as wide as needed.

Then just put your phone number field to the right of it.

OP back. Thank you.

Will the number of dots expand and contract to fit the space?

And can one set the position of the leading data field (left justify)
and ending data field (right justify)?

Appreciate the help.

 

[Guest]

This is not a difficult task. The only real issue you will have is the right
alignment part. That is because most fonts are proportional and the length
of the data string may appear to be different even if all the strings contain
the same number of characters. I would suggest you select a fixed witdth
font. I think Courier is, but I am not sure.

The difficulty with left justifying one part and right justifying the the
other is that a text box or label only has one property for text
justification. That is why I am recommending a fixed font.

Now to the data and the dots. The String function will return a repeating
string of a given character for a given number of characters. For example,
String(5, ".") will return ..... So to get the effect you are after, you
will need to know how long you want the combined string to be, how long then
name part is and how long the phone number part is so you can determine the
number of periods you need to include. Here is an example assuming a total
length of 50:

strName = "John Doe"
strPhone = "(800) 947-2281"

strLine = strName & String(50 - Len(strName) - Len(strPhone), ".") & strPhone

You can adapt it to suit your circumstances, but that is the theory behind it.

 

[Norm]

Just make sure there are enough dots to fill the space. They will only
print as many as will fit in the field. Just make sure the field is as wide
as you want. Then make sure the phone field is butted up against it. This
would be the same as typing something in the first column of excel that is
too wide, then entering something in the second column. The first column
would only show as many characters as would fit.

Thank you.

I think I've got it but not sure.....

As I was trying to explain in my original question, but perhaps poorly,
is that I'd like the output to be in this form:

123 Main Street............................. 203-123-1234


I'd like all the "1s" of 123 to line up (left justified)
then have all the "4s" (the last digit of the phone number) line up
(right justified), and
then fill in the space between with dots/periods depending on the space
available.

So, you are saying that there would be three fields with the middle
containing enough dots to fill any possible resulting space between
address and phone number? But then you say "make sure phone field is
butted up against it." Not sure I understand that part. I'd like to
develop a format that allows for phone numbers to not all be 10 digits.

BTW, this is late to ask this question but "should" I have posted this
question in "forms" or "reports"?

Thank you for the help.

 

[Norm]

Now to the data and the dots. The String function will return a repeating
string of a given character for a given number of characters. For example,
String(5, ".") will return ..... So to get the effect you are after, you
will need to know how long you want the combined string to be, how long then
name part is and how long the phone number part is so you can determine the
number of periods you need to include. Here is an example assuming a total
length of 50:

OP back.

Thank you.

Perhaps then it is not possible since the number of dots needs to be
dependent on the length of the two data fields and not of set length.
Please see my other response for an example.

Appreciate the help.

 

[Guest]

Sorry to butt in here.

I would suggest for your variable number of dots the following:

String(Len(Field1) + Len(Field2), ".")

This will determine the length of the two fields and add them to give you
the correct number of dots.

See if it works for you.

 

[Guest]

The formula I posted does exactly that. You start with a know length. That
would be the counting from where you want the first character to where you
want the last character. I used 50 as an example. You will have to
determine what the length actually is. If you just coded String(50,".") you
would get a string of 50 periods.
Now, we want to put the address at the left of the string and the phone
number at the right of the string. The number of periods needed would be 50
- the number of characters in the address - the number of characters in the
phone number. So, if an address is 25 characters and the phone is 10
characters, you would need to put 15 periods in the middle of the string.
That is how this is constructed:

= strAddress & String(50 - Len(strAddress) - Len(strPhone), ".")

Now you have the address, 15 periods, and the phone number.

As I said before, you will need to use a fixed font to get all lines to line
up in the columns. Proportional fonts would never line up exactly, even if
you could do a left justification for the address and a right justification
for the phone number.

Believe me, this is the only reasonalble way to do this. I even considered
two overlapping text boxes, one left justified and one right justified, but
the problem remains the same and, in fact, would be harder to make work
correctly.

 

[Norm]

Believe me, this is the only reasonalble way to do this. I even considered
two overlapping text boxes, one left justified and one right justified, but
the problem remains the same and, in fact, would be harder to make work
correctly.

Thank you very much.

There was a link posted in the other forum but your tip sounds straight
forward and relatively easy for a non-expert. The only issue could be if
they/we need to use proportional font for style matching reasons with
the balance of the project.

Appreciate the help.

 

[Guest]

Norm,
Norm,
Your question seems similar to one that I have about the text
alignment option in Microsoft Access. I would like to be able to align text
in a text box property pretty much the same way that I can align it when I am
using Excel spreadsheets. Maybe an answer to your question will in someway
be helpful to me.

 

[Arvin Meyer [MVP]]

In Access, you use the property sheet of a form to format text. As a matter
of fact the last 2 versions of Access have more control than Excel over just
how the text resides within a text box. Just open the form in Design View,
then double-click on a textbox to open the property sheet for it. Click on
the Format tab and look at all your possibilities.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access