Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report-line wrapping

    I need to customize the detail section of a report to search the field of each record for "S/N"(white space)'and a number'.
    If that portion of the string won't fit on one line, I want it to wrap to the next line, right before the "S/N"

    I can get most of it but that forced word wrap is stumping me.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    Turning on the CanGrow property of the control will allow it to expand vertically, but it also affects the alignment of other controls. I'd suggest you do any formatting (inserting vbNewLine characters in your field contents) in the query underlying the report. Otherwise, it's going to be hard to get it to break where you want it to.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    The 'Can grow' option is a very familiar thing to me.
    Insert vbNewLine is something entirely new.
    In the query, yet.
    How does that work theoretically?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    vbNewLine is the equivalent of vbCrLf (don't ask me why there are two of them). You can insert it into a string the same way you would insert vbCrLf. It doesn't break the string, it puts a carriage return in it. so you could do something like this (Access 2000 only, I'm afraid) in the query:

    MyCalcField: Replace([MyField],"S/N ", vbNewLine & "S/N ")

    Which would stick a carriage return in ahead of "S/N " in your MyField string. Replace would return the modified value of MyField and you could use that in the report.

    If you wanted to do this in Access 97, you would have to write your own Replace function.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    Unfortunately, I am on Access 97 so that replace function would be an interesting assignment.
    I didn't necessarily want a vbNewLine at every instance of "S/N", but only in the detail section of a report, WHERE the entire line stated "S/N ###########". It presently wraps after the S/N at the whitespace and that sets the proofers off.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    So does it wrap every time? I'm not sure what you mean about setting the "proofers" off. I thought we were talking about the detail section of a report, so maybe I didn't understand you correctly. Is the problem that when the field wraps, it throws off the alignment of fields to the right, or what?

    You have a field in the detail section of the report, and that field contains something like "S/N ###########", right? Is that all it contains or is that only part of the string? Does the length of the string vary between records? You make it sound as though this field might be the only one in the detail section, but I can't tell for sure.
    Charlotte

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Report-line wrapping

    Hi,
    I get the impression from what you've said that the S/N ### could occur anywhere as part of a longer string? If so, I suspect the easiest way might be to strip out the S/N ### into a separate field (assuming you know its maximum possible length, you can size that control accordingly. Alternatively could you replace it with S/N#### (no space) so that it gets treated as one word and wraps properly? You'd still need to build that replace function though I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    Here's an Access 97 Replace function you could use, but be aware that this replaces all instances of the target value in the string:

    <pre>Function Replace(varIn As Variant, _
    varFind As Variant, _
    varNew As Variant)
    'searched varIn for first occurrence
    'of varFind and replaces it with varNew
    On Error Resume Next

    Dim lngFoundPos As Long 'postion of first character of match
    Dim lngFoundLen As Long ' Length of varFind being replaced.

    'Set length of original text to skip.
    lngFindLen = Len(varFind)
    'get the location of matching value.
    lngFoundPos = InStr(1, varIn, varFind)
    'if no matching value found
    'return varIn
    If lngFoundPos = 0 Then
    Replace = varIn
    Else
    'return varIn up to first occurrence of varFind
    'and recurse through rest of varIn replacing
    'all occurrences of varFind with varNew
    Replace = Left(varIn, lngFoundPos - 1) & _
    varNew & Replace(Mid(varIn, lngFoundPos + _
    lngFindLen), varIn, varNew)
    End If
    End Function</pre>

    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    Sorry, The proofer is the proof-reader in our department.
    Unfortunately, it must remain officially as S/N ######## (with the spaces in, or I wouldn't have a problem with it wrapping).

    All other fields respond accordingly. It is the last field and usually dictates the size of each record, because it holds the most information. Oh yeah, most importantly, it's a memo datatype.

    I was just hoping there was a little more control over the auto-wrap aspect of the detail section of a report.
    Thanks

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    Thanks for the Code, Charlotte

    Had a c++ class this weekend. One thing I noticed...code is code.
    Hopefully soon, I maybe able to contribute also.
    You guys are great!! [img]/S/bow.gif[/img]

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    I've noticed the same thing myself.[img]/w3timages/icons/grin.gif[/img] Seriously, I've found over the years that I can read pretty much any kind of code (well, except for Assembler) and understand what's going on in the program. Writing it myself is another issue, of course; but we have to keep programming instructors employed somehow!
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    <font color=blue> Updated vbNewLine schtuff...Ver Access97 SR-2</font color=blue>
    (i.e. NO replace function) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    KILLER>>I need it NOW!!!

    Two things..
    1- I Pass in the expression that includes the field and the argument.
    (e.g. [Address] ",") correct?
    I never got the hang of that expression builder..

    2-How do I use the function, UCase and Trim all in the same expression?
    and in what event?
    (It's imported Titlecase data that must be UCase..)

  13. #13
    Lounger
    Join Date
    Aug 2001
    Location
    Lanham, Maryland
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    I would think that you could specify it as: Upper(Trim(fieldA))

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report-line wrapping

    1. Are you doing this in a query? If so, then you can't use an expression builder, you have to do it the hard way. Furthermore, in a query, you can't use the built-in constants so you have to pass the numeric value they represent instead, but you can use the Chr() function to pass the equivalent. The expression is the calculated field in the query and it would be something like this on the query grid:

    Expr1: Replace([Address], "S/N ",Chr(13) & Chr(10) & "S/N ")

    2. Expr1: UCase(Trim(Replace([Address], "S/N ",Chr(13) & Chr(10) & "S/N ")))

    I'm not sure what you mean about "what event". I thought you were handling this in the query.

    I only used Expr1 as an illustration. Whatever you put in its place will serve as the field name for the resulting expression when you run the query.
    Charlotte

  15. #15
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report-line wrapping

    Sorry my bad for the confusion element.
    I saw where this was applicable in a different scenario.

    This one doesn't involve the "S/N" issue, but just seeing the code was good for both.

    The event I was 'probing' was what type of event could I use this on in a report or label?

    (I originally thought I had to use an event to UCASE everything)
    <font color=red>[Before I knew about =Format([field],">")</font color=red> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    <font color=448800>[Then it was how do I format and Trim?]</font color=448800>
    <font color=blue>[Finally, How do I format, trim, and replace commas with paragraph marks]</font color=blue>

    Expr2: Does it all- thanks
    Technically, I guess I could just type the expression straight into the control source, of the text box in the report.

Page 1 of 2 12 LastLast

Posting Permissions

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