Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    For example, Chr(10) produces a linefeed character.
    If you enter the statement:

    ? "This is an example" & Chr(10) & "of a linefeed
    character."

    in the Immediate/Debug window, the result is
    displayed as:

    This is an example
    of a linefeed character.

    How can I store a line feed within a memo field such as:

    This is an example & Chr(10) & of a linefeed character.

    and get the same line feed result when I print the memo field?

    This is an example
    of a linefeed character.

    Thanks, John

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

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    You will normally enter data into a memo field via a text box on a form. If you set the Enter Key Behavior property of this text box to 'New line in field', pressing Enter while in the text box will not move the focus to the next control, but insert a line feed into the contents. Use tab (or the mouse) to move to the next control. You may also want to set the Scroll Bars property of the text box to Vertical.

    If you ever want to enter a line feed directly into a table or query, use Ctrl+Enter.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Hi Hans

    I knew that, thanks for pointing out the oblivious

    In the same scenario is there any way to store and print a variable imbedded in a text field?

    Example Print "This is an example " & getPref("Profile Code") & " of a linefeed character."

    Returns: This is an example CNF of a linefeed character.

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

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    We're all obvious to the oblivious sometimes <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You could use a unique code within the text to be replaced by an expression. In a query, create a calculated field
    Replace([NameOfField], "UniqueCode", getPref("Profile Code"))
    or even
    Replace([NameOfField], "getPref(""Profile Code"")", getPref("Profile Code"))
    Note the use of double double quotes.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Trying to get the following test query to work and getting following error message, what am I missing?

    str1stLetPar2 contains: This is an example UniqueCode of a linefeed character.

    SELECT Replace([str1stLetPar2],"UniqueCode",getPref("Profile Code")) AS Expr1
    FROM tbl_CR_ViolType
    WHERE (((tbl_CR_ViolType.strViolTypeCode)="0300"));

    Error: Undefined function

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

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    I'm sorry, I wasn't paying attention. The Replace function can't be used in expressions in Access 2000 (it can in Access 2002, which I'm using). See ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions. The solution is to create a custom function that calls Replace in a standard module:

    Public Function MyReplace(strExpression As String, strFind As String, strReplace As String) As String
    MyReplace = Replace(strExpression, strFind, strReplace)
    End Function

    The SQL becomes

    SELECT MyReplace([str1stLetPar2],"UniqueCode",getPref("Profile Code")) AS Expr1
    ...

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Thanks Hans

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Taking this to next step, is there a way to replace multiple variables in a text field?

    str1stLetPar3 contains the following with unique codes GarageName, GarageAddress, GarageCityStateZip
    and GaragePhone:

    Failure to comply with this directive whitin the specified time will necessitate further action by this
    municipality, including court appearence tickets or vehicle removal. If Your vehicle is removed or
    cause to be removed by the City, you are to contact GarageName, GarageAddress, GarageCityStateZip
    or telephone GaragePhone. You will be required to pay the costs of removal and storage to reclaim
    this vehicle. Ownership of the vehicle, if unclaimed, shall vest in the City.

    SELECT MyReplace([str1stLetPar3],"GarageName",getPref("Complaint MV Garage Name")) AS Expr1
    FROM tbl_CR_ViolType
    WHERE (((tbl_CR_ViolType.strViolTypeCode)="0400"));

    Returns:

    Failure to comply with this directive whitin the specified time will necessitate further action by this
    municipality, including court appearence tickets or vehicle removal. If Your vehicle is removed or
    cause to be removed by the City, you are to contact Ben

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

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    You'll have to use MyReplace for each replacement:

    SELECT MyReplace(MyReplace(MyReplace(MyReplace([str1stLetPar3],],"GarageName",getPref("Complaint MV Garage Name")),"GarageAddress",getPref("Complaint MV Garage Address")),"GarageCityStateZip",getPref("Complaint MV Garage City State Zip")),"GaragePhone",getPref("Complaint MV Garage Phone"))

    (I haven't actually tried this)

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

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    In addition to my previous reply: it might be more efficient to write a custom function to do all the replacements, instead of calling the generic MyReplace several times. If you have to replace one or two other codes, the expression would become very cumbersome, and hard to read. Something like this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Custom function in a standard module:

    Public Function ProcessValue(strValue As String) As String
    Dim strTemp As String
    strTemp = strValue
    strTemp = Replace(strTemp, "GarageName", getPref("Complaint MV Garage Name"))
    strTemp = Replace(strTemp, "GarageAddress", getPref("Complaint MV Garage Address"))
    strTemp = Replace(strTemp, "GarageCityStateZip", getPref("Complaint MV Garage City State Zip"))
    strTemp = Replace(strTemp, "GaragePhone", getPref("Complaint MV Garage Phone"))
    ProcessValue = strTemp
    End Function

    This can easily be extended to handle any number of replacements, if necessary. Of course, more replacements will have a negative impact on performance, but this will probably only be noticeable if you have a large number of records.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Use in query:

    SELECT ProcessValue([str1stLetPar3]) As Expr1
    FROM ...

    <img src=/w3timages/blueline.gif width=33% height=2>

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Hi Hans

    This is exactly what I was looking for.

    Trying to setup a simple user defined letter library, where the user creates the letter and provides the text and I provide at list of unique codes that point to table driven variables and data driven variables for simple letter creation.

    This is a good starting point.

    <pre>Paragraph5 = _
    vbCr + vbLf & _
    ProcessValue(DLookup("[str1stLetPar2]", _
    "[tbl_CR_ViolType]", _
    "[lngViolTypeID] = report![lngViolTypeID]")) _
    & vbCr + vbLf
    </pre>



    Thanks, John

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    If interested, another option for this. For more flexibility, use arrays as arguments to function. Example:

    <code>Public Function DetokenizeText(ByVal strTxt As String, _</code>
    <code> ByRef Token() As String, _</code>
    <code> ByRef Text() As String, _</code>
    <code> Optional CompareMethod As VbCompareMethod = vbBinaryCompare) As String</code>
    <code> On Error GoTo Err_Handler</code>
    <code> </code>
    <code> Dim n As Long</code>
    <code> Dim strMsg As String</code>
    <code> </code>
    <code> ' Zero-based array is expected:</code>
    <code> For n = 0 To UBound(Token)</code>
    <code> If InStr(1, strTxt, Token(n), CompareMethod) > 0 Then</code>
    <code> strTxt = Replace(strTxt, Token(n), Text(n), , , CompareMethod)</code>
    <code> End If</code>
    <code> Next n</code>
    <code> DetokenizeText = strTxt</code>
    <code> </code>
    <code>Exit_Sub:</code>
    <code> Exit Function</code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> Beep</code>
    <code> MsgBox strMsg, vbExclamation, "ERROR MESSAGE"</code>
    <code> Resume Exit_Sub</code>
    <code>End Function</code>

    Test sub:

    <code>Public Sub TestDetokenizeText()</code>
    <code></code>
    <code> Dim Token(2) As String</code>
    <code> Dim Text(2) As String</code>
    <code> Dim strTxt As String</code>
    <code> </code>
    <code> Token(0) = "#date#"</code>
    <code> Token(1) = "$name$"</code>
    <code> Token(2) = "$city$"</code>
    <code> </code>
    <code> Text(0) = Format(Date, "dd mmm yyyy")</code>
    <code> Text(1) = "Mr. John Smith-Beta-Jones"</code>
    <code> Text(2) = "New York"</code>
    <code> </code>
    <code> strTxt = "On #date# the subject, $name$ will arrive in the city of $city$. " & _</code>
    <code> "$name$ will depart $city$ at 8 pm on the day immediately following #date#, " & _</code>
    <code> "if all proceeds as scheduled. If not, $Name$ will experience a bad day."</code>
    <code> </code>
    <code> Debug.Print strTxt</code>
    <code> Debug.Print</code>
    <code> Debug.Print DetokenizeText(strTxt, Token, Text) '$Name$ will not be replaced</code>
    <code> Debug.Print</code>
    <code> Debug.Print DetokenizeText(strTxt, Token, Text, vbTextCompare) '$Name$ will be replaced</code>
    <code> </code>
    <code>End Sub</code>

    Note use of optional CompareMethod argument; a binary (case-sensitive) comparison is the default. Test sub demonstrates use of this argument to ignore case if desired (text comparison). Of course the "tokens" do not need to be delimited by symbols, that just makes it more obvious where the "variables" are, not unlike defining fields in a Word document (I originally used % signs but that did not display properly in HTML). You may be able to adapt this.

    HTH

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Print a Line Feed Imbedded In Memo Field? (a2k (9.0.6926) SP-3

    Mark

    Great code, I will try it in next project

    Thank, John

Posting Permissions

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