Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Find Paragraph Start And End Then Replace

    Hi Guys, wonder if you can advise with this one please ?

    I want to find the last word called:



    Engineers.

    Replace the full stop and add additional info from a string

    ie

    Line that starts with "We" and Ends In "Engineers."

    User has an option to add more info to that paragraph

    My String that adds will now be

    engineers, but i have now added this text from a string as i selected number 4 from an input box to add this line.

    Hope this makes sense !!

    Kindest

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use Instr(), Left(), Mid() & Replace() functions
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'd add that to find the last iteration of I'd also look at the instrrev() function.

    Kind of understand what you are requiring but the description is not clear - typically paragraphs start on a new line, but sentences may not. So rather than describing your data, provide an example of before and after - the before needs to be the entire text - something like

    before "We are the engineers."

    after "We are the engineers are on holiday."

    is not sufficient. Also, why can't your users just edit the text?

    it may be you need to look at the split function

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guy's thank you the code is a bit long winded but after selecting an option for an email reply, this is an example of the result (Some of the text is replaced on here)

    Code:
    Good Morning Name, 
    
    Thank you for visiting us at http://www.ourwebsite.com and sending your enquiry
    
    
    The great news is that we can assist.
    
    
    We offer most Item removed to the trade.
    
    
    Your product we are able to sell to a minority of traders in the market (not for a huge financial amount) but enough of a margin where we are able to donate something.
    
    
    We are able to offer you £999.99 for your product which is inclusive of work carried out by our team of engineers.
    
    
    We have got options to book throughout week commencing Monday-05-Jul-2021, however, due to existing enquiries, we can fill these spaces relatively quickly
    
    
    If you wish to confirm your booking with us, you can do this online at http://www.ourwebsite.com/online-booking
    
    
    We look forward to hearing from you
    
    
    With Kind Regards
    if an web enquiry has not specified an item correctly, i now want to keep but also change the line in bold

    So the line/sentence/paragraph to keep and change is from:

    We are able to offer you £999.99 for your product which is inclusive of work carried out by our team of engineers.

    To:

    myString = engineers, however as you have not submitted the item number, we would need this number in order for us to commit to this offer."

    We are able to offer you £999.99 for your product which is inclusive of work carried out by our team of engineers.

    now becomes:We are able to offer you £999.99 for your product which is inclusive of work carried out by our team of & mystring

    Hope this makes sense ?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    would have thought this would be better resolved when the text is initially created but I guess that is not an option. But more questions

    'engineers.' only appears once in the text so why can't you just use replace?

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ajax, thank you, so i guess can you demonstrate the method of finding the word engineers. and replace with a string

    so i guess engineers. is the start of a new string but changing the full stop to a comma

    I think i am struggling with the method of finding the word "engineer." via a string then replace with the new string

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by DMT Dave View Post
    Hi ajax, thank you, so i guess can you demonstrate the method of finding the word engineers. and replace with a string

    so i guess engineers. is the start of a new string but changing the full stop to a comma

    I think i am struggling with the method of finding the word "engineer." via a string then replace with the new string
    Look at the Replace() function and it's parameters. As long as engineers. only exists once in the text, that is the simplest solution?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi welshgasman, I think I am losing myself in the method to do it

    This is what I have done but doesn't replace the part on the text box txtReply

    Code:
    Dim orgStr as String, newStr as String, myStr as String
    newStr = "engineer, as you have not submitted an item number, we can honour the offer based on the item number, is it possible for you to provide this please ?."
    orgStr = Me.txtReply
    myStr = Replace(orgString, "Engineer.", newStr)

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe try:

    Code:
    Dim orgStr as String, newStr as String, myStr as String
    newStr = "engineer, as you have not submitted an item number, we can honour the offer based on the item number, is it possible for you to provide this please ?"
    orgStr = Me.txtReply
    myStr = Replace(orgString, " engineers.",  " engineers" & newStr)
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is what I have done but doesn't replace the part on the text box txtReply
    If your Option Compare statement is binary, make sure you're looking for the exact capitalization in a word. It will not find Engineer in a string that only contains engineer if you are doing binary comparisons.
    if an web enquiry has not specified an item correctly
    Does this mean that the textbox is unbound? If so, I suspect that orgStr is Null, in which case you'd need to set focus to it and use the text property.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks Vlad, the txtReply doesn't change the wording of newStr, i did notice that I had set the Dim As OrgString, then shortened to orgStr, chanegd that in your sample but doesn't change the wording

    Code:
    newStr = "as you have not submitted an item number, we can honour the offer based on the item number, is it possible for you to provide this please ?"orgStr = Me.txtReply
    myStr = Replace(orgStr, " engineers.", " engineers, " & newStr)
    Do i need Me.txtReply now = something afterwards or should replace function just auto replace the wording around and after the keyword of engineers. ?

  12. #12
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Micron, thank you, when i scroll to the top, the first line is Option Compare Database, does this confirm ?

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes. Instr won't care what the capitalization is as long as you don't specify a binary compare option in the function because it defaults to textual.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Dave,
    Please review this:
    Code:
    Dim orgStr as String, newStr as String, myStr as String
    
    
    newStr = "as you have not submitted an item number, we can honour the offer based on the item number, is it possible for you to provide this please ?"
    orgStr = Me.txtReply
    myStr = Replace(orgStr, " engineers.", " engineers, " & newStr)
    Me.txtReply=myStr
    
    
    'or simply
    'Me.txtReply=Replace(Me.txtReply, " engineers.", " engineers, " & newStr)
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by DMT Dave View Post
    Thanks Vlad, the txtReply doesn't change the wording of newStr, i did notice that I had set the Dim As OrgString, then shortened to orgStr, chanegd that in your sample but doesn't change the wording

    Code:
    newStr = "as you have not submitted an item number, we can honour the offer based on the item number, is it possible for you to provide this please ?"orgStr = Me.txtReply
    myStr = Replace(orgStr, " engineers.", " engineers, " & newStr)


    Do i need Me.txtReply now = something afterwards or should replace function just auto replace the wording around and after the keyword of engineers. ?
    Think about it?
    You want Me.TxtReply to be the value after you have replaced the text?
    How do you think that is going to happen, unless you tell Access?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find and Replace
    By Keibri in forum Access
    Replies: 3
    Last Post: 05-31-2019, 11:43 AM
  2. you can't use find or replace now
    By ntambomvu in forum Access
    Replies: 2
    Last Post: 10-25-2017, 02:58 PM
  3. Find and Replace
    By dweekley in forum Queries
    Replies: 3
    Last Post: 04-12-2013, 07:16 AM
  4. find and replace
    By rohini in forum Access
    Replies: 7
    Last Post: 05-17-2012, 05:23 AM
  5. Find and Replace
    By Bedsingar in forum Access
    Replies: 1
    Last Post: 08-14-2011, 01:10 PM

Posting Permissions

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