Results 1 to 11 of 11

Access 2010 Report Find and replace from table

  1. #1
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24

    Access 2010 Report Find and replace from table

    Good day,

    I have a table with field "description" and "comments" both are memo.
    Another table "List" with keyword.
    In a report concatenate the two fields (description and comments) in one "description".
    Now I need any keyword from the table "List" to wrap text.

    Example - List table (keyword)
    Not available
    Offer:
    Delivery time:

    Example Report


    Description: AAAAA
    Comments: BBBBB Offer: XXXX Delivery time: 2 working days
    Concatenate: AAAAA BBBBB Offer: XXXX Delivery time: 2 working days
    Request in the report the field "description" to be:

    AAAAA BBBBB (wrap text - new line)
    Offer: XXXX (wrap text - new line)
    Delivery time: 2 working days


    Thank you in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    You want to insert carriage return and linefeed into the Comments string? Will need VBA code custom function to modify the string.

    This is why distinct data should be in separate fields. Concatenation of fields is easier than splitting data.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24
    Thank you for your reply June7.
    Unfortunately I donít know how to do that.
    Can you help me?
    Thank you in advance.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    Just had nova flare up in my head. This query will produce the result you want:

    SELECT *, Replace(Replace([Comments],"Offer",Chr(13) & Chr(10) & "Other"),"Delivery",Chr(13) & Chr(10) & "Delivery") AS T
    FROM Table1;
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24
    Dear June7,
    Actually I use the following in the control source of the report in the field "description".

    =IIf(DCount("[fWrap]","List","[fWrap] Like '*" & Left([Comment],2) & "*'"),[Description] & Chr(13) & Chr(10) & [Comment],[Description] & " " & [Comment])

    The above code no work correctly as I need (see example 1).
    The correct way must be as example 2

    Click image for larger version. 

Name:	Example-1.jpg 
Views:	2 
Size:	13.1 KB 
ID:	10031

    Click image for larger version. 

Name:	Example-2.jpg 
Views:	2 
Size:	17.3 KB 
ID:	10032

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    Use the Replace expression in query or in textbox ControlSource.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24
    I understand but I have to use the "List" tabletwith the keyword as the List tablet increase with additional items.
    How I can use the List table instead of single words"Delivery time:, Offer:" etc.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    Don't understand any of that last post. Want to try again?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24
    I am sorry for misunderstanding.
    Please read the first my post, I have a table with keyword (List.tab).
    In this table are the words that every time that are included in the description or comment has to create a new line.
    That is the reason that I cannot use only a few words in the replace function as you have indicated me.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    That clarifies the original post. VBA only solution I can see.
    Code:
    Function WrapString(strCom)
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        rs.Open "SELECT fWrap FROM List;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        While Not rs.EOF
            strCom = Replace(strCom, rs!fWrap, vbCrLf & rs!fWrap)
            rs.MoveNext
        Wend
        WrapString = strCom
    End Function
    Call the function from query or textbox: WrapString(Nz([Comments],""))
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  11. #11
    devcon is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    24
    Thank you very much.
    Working perfectly.
    Have a nice weekend.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. find and replace
    By rohini in forum Access
    Replies: 7
    Last Post: 05-17-2012, 04:23 AM
  2. Find and Replace Within a Macro
    By bmschaeffer in forum Access
    Replies: 1
    Last Post: 12-20-2011, 10:27 AM
  3. Find and Replace
    By Bedsingar in forum Access
    Replies: 1
    Last Post: 08-14-2011, 12:10 PM
  4. Find and Replace query from a 2nd table
    By elightbox in forum Queries
    Replies: 1
    Last Post: 09-17-2010, 04:37 PM
  5. Find and Replace Query
    By randolphoralph in forum Queries
    Replies: 4
    Last Post: 03-17-2010, 06:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums