Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report: Shrink text, if text longer than container

    G'day people,

    I'm working on a rather complicated report, it's going quite well so far and I solved many problems which I wanted to ask here first [img]/forums/images/smilies/wink.gif[/img] Well here's one I can't figure out myself:
    I'm creating a table with the report function, and since it's layout its fixed some records are longer than the table cell. What I would like Access to do now:
    If record > 14 letters either shrink textsize (and make it even 2 lines instead of one) or make a dot after those 14 letters.
    The first method is clearly preferred, since even text with size 7 is readable and I'm working with size 12 now - plenty of room to shrink and make two lines.

    I guess this is only possible via code. I'm poking around VB with SelLength and Len(Me.MyTextBox.Value), but no luck.

    Greetings
    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report: Shrink text, if text longer than container

    You can use code in the On Format event of the section containing the text box (presumably the Detail section):

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Len(Me.txtSomething) > 14 Then
    Me.txtSomething.FontSize = 7
    Else
    Me.txtSomething.FontSize = 12
    End If
    End Sub

    You could also create a more complicated decision tree.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report: Shrink text, if text longer than conta

    That's it Hans!

    One more question about this though: How do I split the box into 2 lines then? Using vbCrLf of course, but how to make it let's say the middle of the text, or even better - where a space is?

    I have for example "International Greenhouse". How do I do it that it get split up into

    "International
    Greenhouse"
    (by taking in account the space)

    Dennis

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report: Shrink text, if text longer than conta

    You could put the following in the control's source. The control's name in this example is str.

    =iif(Len(str)>14, Mid(str, 1, Instr(1, str, " ",2)-1) & vbCrLf & Mid(str, Instr(1, str, " ",2)+1), str)

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report: Shrink text, if text longer than conta

    Did I understand you right that I am supposed to put that inthe the control's source? Where does the original source go then, if you say str is the name?

    The textbox's previous control source was [Country] - i tried replacing the str with Country, but it doesn't really make sense as you say it's supposed to be the name... I guess you have to help out the stupid one here, even though I see the point in your statement, which is looking for a space and entering a cbCrLf then... anyways [img]/forums/images/smilies/wink.gif[/img]

    Dennis

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report: Shrink text, if text longer than conta

    I meant that str would replace the old control source. If the control source was Country then try:

    =iif(Len(Country )>14, Mid(Country , 1, Instr(1, Country , " ",2)-1) & vbCrLf & Mid(Country , Instr(1, Country , " ",2)+1), Country )

    If anyone is stupid it's probably me for not understanding what you want.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report: Shrink text, if text longer than conta

    Gives me the invalid Syntax Error.

    Ok again what I am trying(even though I found a nice fond already which does the same):
    Rule: If the text in txtCountry (Control Source [Country]) is longer than 14 it WILL contain at least one space

    Now what I want: If text in txtCountry is longer than 14, devide at the first space and make the rest a new line.
    Example:
    "United States of America" would become

    "United
    States of America"

    or even
    "United States
    of America"

    Hope it's understandable now. This would be so much easier in any other program including webdesign. You just define a container and it wraps the text, so it fits... Access hmm

    Dennis

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report: Shrink text, if text longer than conta

    You can't use vbCrLf in the control source of a text box, so try

    =IIf(Len([Country])>14,Left([Country],Instr([Country]," ")-1) & Chr(13) & Chr(10) & Mid([Country],Instr([Country]," ")+1),[Country])

    Notes:
    1. Replace the comma with the list separator on your system. If you use comma as decimal separator, you probably have the semicolon <code>;</code>
    as list separator.
    2. Make sure the text box is high enough to contain multiple lines.
    3. Since the control source is a formula that refers to Country, the text box should not be named Country. That would confuse Access.
    4. Access *does* wrap text in a text box automatically.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report: Shrink text, if text longer than conta

    1. Replacing the comma did the trick (an replacing cbCrLf)!
    4. You are right. Access DOES word-wrap. Only problem is that you cannot really tell it where to wordwrap - with this I am trying to get some order in this mess.

    Ok I tried both methods, and actually I can go with a fontsize of 10 for all items now (with the space-new-line method). With the shrinking method I was only able to have fontsize 7 for the longer ones and 11 for the bigger ones, so I'm totally happy with that. I think I will still be using the shrinking method for even longer texts, let's say >25

    Thanks to both of you for the help!

    Dennis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •