Results 1 to 10 of 10
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    partial update query

    Hi



    Have a ta;e field which contains a value of

    www.xyz.co.uk/n.pdf

    The www.xyz.co.uk is in every record but the n.pdf could be any pdf file.

    The web address has now changed to

    abc.co.uk but the pdf name has not changed.

    Is there an update query which allows a partial field update?

    thanks

    Ian

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the value a text value or a hyperlink?

    If the former a simple replace function should do it.

    UPDATE myTable
    SET fld1=replace(fld1,"xyz.co.uk","abc.co.uk")

  3. #3
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    thanks for the response - as usual helpful and a pertinant question ;-)

    IN the table the field is designated as short text

    When the field is shown on the form I've added and event

    Code:
    Private Sub pdfMap_Click()
    Dim strPath As String
    strPath = Me.pdfMap.Value
    FollowHyperlink strPath
    End Sub
    So I assume your code will work perfectly but is this the best way to access pdf maps pn the web?

    thanks

    Ian

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know the answer to that, all depends on what you want to happen - you could assign the path to a webbrowser control for example, but don't know if that will give the result you want

    You don't need .value, that is the default and you don't need Me. since that is also a given and you don't need strpath just to reassign it

    FollowHyperlink pdfMap

    is all that is required

  5. #5
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Thanks for the assist

    just goes to show that a search via google does not always reveal the simplest way to achieve something ;-)

    Ian

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Ajax View Post
    you don't need Me. since that is also a given

    FollowHyperlink pdfMap
    It's personal preference, but I always use the "Me" when referring to form controls. It may not be required, but it's more "self-documenting" to me. If I saw

    FollowHyperlink pdfMap

    in the middle of some code, I'd wonder what pdfMap was: a variable, control, function, etc.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    code check please

    Hi

    I suspect I have my code or rather my implementation of your code wrong.

    My table is name tblDeceased

    The field name is Map

    The entries in the field are in the format short text and are for example

    http://www.edwards-micros.co.uk/wsmfhs/p.pdf
    http://www.edwards-micros.co.uk/wsmfhs/q.pdf
    http://www.edwards-micros.co.uk/wsmfhs/r.pdf

    The PDFs are named a up to ff ( after areas in Graveyard)

    The new domain will be www.wsmfhs.org.uk


    so the field will need to read

    www.wsmfhs.org.uk/wsmfhs/p.pdf
    www.wsmfhs.org.uk/wsmfhs/q.pdf
    www.wsmfhs.org.uk/wsmfhs/r.pdf

    So I used the query

    Code:
    UPDATE tbldeceased 
    SET Map = replace("http://www.edwards-micros.co.uk","http://www.edwards-micros.co.uk","http://wsmfhs.org.uk");
    THis works except that it updates the fields to

    www.wsmfhs.org.uk

    ie leaving off the /p.pdf or/r.pdf etc.

    I do realize I could use the find and replace tool but I really would like to know how to do it is a query or to put it another way it's bugging me what I have missed

    thanks

    Ian

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, the first argument should be the field name, not a literal string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Thanks that sorted it

    Ian

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 03-10-2016, 01:01 PM
  2. Partial Match Query
    By punna111 in forum Queries
    Replies: 10
    Last Post: 08-20-2014, 03:35 PM
  3. Replies: 3
    Last Post: 06-11-2014, 08:06 AM
  4. Query for Partial Uppercase Text in Field
    By pjordan@drcog.org in forum Queries
    Replies: 15
    Last Post: 12-29-2011, 12:53 PM
  5. Partial SUM of values on a query.
    By jrvf in forum Queries
    Replies: 2
    Last Post: 08-16-2010, 01:33 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