Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBNewLine in text field (97-sr2)

    I'm trying to write a vbNewLine into a text field of a label based on an instr function.
    It should look for a hyphen and replace that with vbNewline.
    How do I wrap it inside the following code?
    <font color=blue>=Format(Trim([Field]),">")</font color=blue>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBNewLine in text field (97-sr2)

    Use the Replace function:

    Replace ([Field],"-",vbNewline)
    Francois

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

    Re: VBNewLine in text field (97-sr2)

    There's no replace function in Access 97. It was introduced in Access 2000. In 97, you have to write your own, or search the Lounge on replace.

    I'm not sure what was meant by the "text field of a label". Is this a textbox or a label? Is this question in any way related to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=19525&page=& view=&sb=&o=&vc=1#Post19525>this thread</A>, where replace was discussed?
    Charlotte

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

    Re: VBNewLine in text field (97-sr2)

    Ooooh, you are sharp Charlotte!
    I plum forgot about that one.
    It's not directly related to that project,
    but it definitely will do the job.
    Thanks again <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: VBNewLine in text field (97-sr2)

    I'm getting extra spaces in the newly created lines on the reports.

    <font color=blue>Address_Format: NZ(Replace([Address],"=",Chr(13) & Chr(10) & ""))</font color=blue>

    I use to have a TRIM in both areas, but thought they might conflict each other.
    It is now in the controlsource of the textbox in the report only.

    <font color=red>=Format(Trim([Address_Format]),">")</font color=red>

    If I am running the REPLACE function in the query,
    where can I most effectively TRIM the results of the output ?

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

    Re: VBNewLine in text field (97-sr2)

    Well, first, why are you using an NZ? If you're likely to have replace return a Null, then I would wrap the [Address] field in the NZ and pass it an empty string as the replacement argument. Second, you don't need the empty string after the two chr() expressions in Access.

    Trim shouldn't conflict with anything, but I would suggest you try some testing to see where those spaces are coming from. It may be that you have some in the Address field and therefore need to wrap *it* in a Trim before you pass it into the Replace function.
    Charlotte

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

    Re: VBNewLine in text field (97-sr2)

    Yes, there are definitely some extra white spaces in there.
    I zipped through the records and replaced (Ctl+H) my delimiter with a new char (=).
    It must've had a space in there.
    I added the NZ in the query on a hunch because I was getting errors
    when I tried to create a new line out of nothing.
    Since the replace function was declaring a variable,
    I thought you needed to use the NZ to make it handle the nulls....ah well live and learn <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Posting Permissions

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