Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to use update query for partial updating of a field

Discussion in 'Information Technology' started by Veli Izzet, Oct 31, 2005.

  1. Veli Izzet

    Veli Izzet
    Expand Collapse
    Guest

    Hi all,

    I want to partially update a text field; i.e. changing only a few words
    of a sentence in that field.

    Up to now, I exported the table to excell, do search&replace there and
    imported back. This is clumsy and I am afraid there may be some
    corruption in the table, so I want to be able to do it in access. I am
    using the 2003 version.

    How do I do this?

    Regards
     
  2. Loading...


  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    You can use the Replace() function in an update query. For example, the
    following query will replace all instances of the abbreviation "Ave." in the
    Address field in the Employees table with the word "Avenue" ...

    UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");

    In query design view, the expression in the "Update to" row looks like this
    ....

    Replace([Address],"Ave.","Avenue")

    --
    Brendan Reynolds

    "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    > Hi all,
    >
    > I want to partially update a text field; i.e. changing only a few words of
    > a sentence in that field.
    >
    > Up to now, I exported the table to excell, do search&replace there and
    > imported back. This is clumsy and I am afraid there may be some corruption
    > in the table, so I want to be able to do it in access. I am using the 2003
    > version.
    >
    > How do I do this?
    >
    > Regards
     
  4. Veli Izzet

    Veli Izzet
    Expand Collapse
    Guest

    Thanks..
    Brendan Reynolds wrote:
    > You can use the Replace() function in an update query. For example, the
    > following query will replace all instances of the abbreviation "Ave." in the
    > Address field in the Employees table with the word "Avenue" ...
    >
    > UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");
    >
    > In query design view, the expression in the "Update to" row looks like this
    > ...
    >
    > Replace([Address],"Ave.","Avenue")
    >
     
  5. Guest

    Guest
    Expand Collapse
    Guest

    Unless your computer, like mine, for some obscure
    reason related to version, installation history, and security
    patches, refuses to let you use Replace() in a query.

    In which case you will have to write a VBA function
    to call replace:

    Function MyReplace(sSource,sFind,Sreplace) as string
    MyReplace = replace(sSource,sFind,Sreplace)
    end function

    (david)

    "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    > You can use the Replace() function in an update query. For example, the
    > following query will replace all instances of the abbreviation "Ave." in

    the
    > Address field in the Employees table with the word "Avenue" ...
    >
    > UPDATE Employees SET Employees.Address =

    Replace([Address],"Ave.","Avenue");
    >
    > In query design view, the expression in the "Update to" row looks like

    this
    > ...
    >
    > Replace([Address],"Ave.","Avenue")
    >
    > --
    > Brendan Reynolds
    >
    > "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    > news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    > > Hi all,
    > >
    > > I want to partially update a text field; i.e. changing only a few words

    of
    > > a sentence in that field.
    > >
    > > Up to now, I exported the table to excell, do search&replace there and
    > > imported back. This is clumsy and I am afraid there may be some

    corruption
    > > in the table, so I want to be able to do it in access. I am using the

    2003
    > > version.
    > >
    > > How do I do this?
    > >
    > > Regards

    >
    >
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    I know that used to happen with Access 2000, David. But the original poster
    specified Access 2003, and I've never heard of a problem using the Replace()
    function in a query in Access 2003. Are you seeing the problem in Access
    2003?

    --
    Brendan Reynolds

    <david@epsomdotcomdotau> wrote in message
    news:%23NLdeAU3FHA.3636@TK2MSFTNGP09.phx.gbl...
    > Unless your computer, like mine, for some obscure
    > reason related to version, installation history, and security
    > patches, refuses to let you use Replace() in a query.
    >
    > In which case you will have to write a VBA function
    > to call replace:
    >
    > Function MyReplace(sSource,sFind,Sreplace) as string
    > MyReplace = replace(sSource,sFind,Sreplace)
    > end function
    >
    > (david)
    >
    > "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    > news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    >> You can use the Replace() function in an update query. For example, the
    >> following query will replace all instances of the abbreviation "Ave." in

    > the
    >> Address field in the Employees table with the word "Avenue" ...
    >>
    >> UPDATE Employees SET Employees.Address =

    > Replace([Address],"Ave.","Avenue");
    >>
    >> In query design view, the expression in the "Update to" row looks like

    > this
    >> ...
    >>
    >> Replace([Address],"Ave.","Avenue")
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    >> news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    >> > Hi all,
    >> >
    >> > I want to partially update a text field; i.e. changing only a few words

    > of
    >> > a sentence in that field.
    >> >
    >> > Up to now, I exported the table to excell, do search&replace there and
    >> > imported back. This is clumsy and I am afraid there may be some

    > corruption
    >> > in the table, so I want to be able to do it in access. I am using the

    > 2003
    >> > version.
    >> >
    >> > How do I do this?
    >> >
    >> > Regards

    >>
    >>

    >
    >
     
  7. Veli Izzet

    Veli Izzet
    Expand Collapse
    Guest

    Well, it seems I am one of the chosen ones.... I succeeed using replace..

    david@epsomdotcomdotau wrote:
    > Unless your computer, like mine, for some obscure
    > reason related to version, installation history, and security
    > patches, refuses to let you use Replace() in a query.
    >
    > In which case you will have to write a VBA function
    > to call replace:
    >
    > Function MyReplace(sSource,sFind,Sreplace) as string
    > MyReplace = replace(sSource,sFind,Sreplace)
    > end function
    >
    > (david)
    >
    > "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    > news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    >
    >>You can use the Replace() function in an update query. For example, the
    >>following query will replace all instances of the abbreviation "Ave." in

    >
    > the
    >
    >>Address field in the Employees table with the word "Avenue" ...
    >>
    >>UPDATE Employees SET Employees.Address =

    >
    > Replace([Address],"Ave.","Avenue");
    >
    >>In query design view, the expression in the "Update to" row looks like

    >
    > this
    >
    >>...
    >>
    >>Replace([Address],"Ave.","Avenue")
    >>
    >>--
    >>Brendan Reynolds
    >>
    >>"Veli Izzet" <veli.izzet@gmail.com> wrote in message
    >>news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    >>
    >>>Hi all,
    >>>
    >>>I want to partially update a text field; i.e. changing only a few words

    >
    > of
    >
    >>>a sentence in that field.
    >>>
    >>>Up to now, I exported the table to excell, do search&replace there and
    >>>imported back. This is clumsy and I am afraid there may be some

    >
    > corruption
    >
    >>>in the table, so I want to be able to do it in access. I am using the

    >
    > 2003
    >
    >>>version.
    >>>
    >>>How do I do this?
    >>>
    >>>Regards

    >>
    >>

    >
    >
     
  8. Ken Snell [MVP]

    Ken Snell [MVP]
    Expand Collapse
    Guest

    And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.

    --

    Ken Snell
    <MS ACCESS MVP>

    "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    news:%23JtqYJU3FHA.732@TK2MSFTNGP10.phx.gbl...
    >I know that used to happen with Access 2000, David. But the original poster
    >specified Access 2003, and I've never heard of a problem using the
    >Replace() function in a query in Access 2003. Are you seeing the problem in
    >Access 2003?
    >
    > --
    > Brendan Reynolds
    >
    > <david@epsomdotcomdotau> wrote in message
    > news:%23NLdeAU3FHA.3636@TK2MSFTNGP09.phx.gbl...
    >> Unless your computer, like mine, for some obscure
    >> reason related to version, installation history, and security
    >> patches, refuses to let you use Replace() in a query.
    >>
    >> In which case you will have to write a VBA function
    >> to call replace:
    >>
    >> Function MyReplace(sSource,sFind,Sreplace) as string
    >> MyReplace = replace(sSource,sFind,Sreplace)
    >> end function
    >>
    >> (david)
    >>
    >> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    >> news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    >>> You can use the Replace() function in an update query. For example, the
    >>> following query will replace all instances of the abbreviation "Ave." in

    >> the
    >>> Address field in the Employees table with the word "Avenue" ...
    >>>
    >>> UPDATE Employees SET Employees.Address =

    >> Replace([Address],"Ave.","Avenue");
    >>>
    >>> In query design view, the expression in the "Update to" row looks like

    >> this
    >>> ...
    >>>
    >>> Replace([Address],"Ave.","Avenue")
    >>>
    >>> --
    >>> Brendan Reynolds
    >>>
    >>> "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    >>> news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    >>> > Hi all,
    >>> >
    >>> > I want to partially update a text field; i.e. changing only a few
    >>> > words

    >> of
    >>> > a sentence in that field.
    >>> >
    >>> > Up to now, I exported the table to excell, do search&replace there and
    >>> > imported back. This is clumsy and I am afraid there may be some

    >> corruption
    >>> > in the table, so I want to be able to do it in access. I am using the

    >> 2003
    >>> > version.
    >>> >
    >>> > How do I do this?
    >>> >
    >>> > Regards
    >>>
    >>>

    >>
    >>

    >
    >
     
  9. Guest

    Guest
    Expand Collapse
    Guest

    It was certainly fixed on my computer - and stopped working
    again sometime after applying SP8. I'm not presently at that
    computer. I'll try again with 2K2 and 2K3 when I get a chance.

    (david)

    "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    news:uXAoXGa3FHA.1596@tk2msftngp13.phx.gbl...
    > And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    > news:%23JtqYJU3FHA.732@TK2MSFTNGP10.phx.gbl...
    > >I know that used to happen with Access 2000, David. But the original

    poster
    > >specified Access 2003, and I've never heard of a problem using the
    > >Replace() function in a query in Access 2003. Are you seeing the problem

    in
    > >Access 2003?
    > >
    > > --
    > > Brendan Reynolds
    > >
    > > <david@epsomdotcomdotau> wrote in message
    > > news:%23NLdeAU3FHA.3636@TK2MSFTNGP09.phx.gbl...
    > >> Unless your computer, like mine, for some obscure
    > >> reason related to version, installation history, and security
    > >> patches, refuses to let you use Replace() in a query.
    > >>
    > >> In which case you will have to write a VBA function
    > >> to call replace:
    > >>
    > >> Function MyReplace(sSource,sFind,Sreplace) as string
    > >> MyReplace = replace(sSource,sFind,Sreplace)
    > >> end function
    > >>
    > >> (david)
    > >>
    > >> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in

    message
    > >> news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    > >>> You can use the Replace() function in an update query. For example,

    the
    > >>> following query will replace all instances of the abbreviation "Ave."

    in
    > >> the
    > >>> Address field in the Employees table with the word "Avenue" ...
    > >>>
    > >>> UPDATE Employees SET Employees.Address =
    > >> Replace([Address],"Ave.","Avenue");
    > >>>
    > >>> In query design view, the expression in the "Update to" row looks like
    > >> this
    > >>> ...
    > >>>
    > >>> Replace([Address],"Ave.","Avenue")
    > >>>
    > >>> --
    > >>> Brendan Reynolds
    > >>>
    > >>> "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    > >>> news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    > >>> > Hi all,
    > >>> >
    > >>> > I want to partially update a text field; i.e. changing only a few
    > >>> > words
    > >> of
    > >>> > a sentence in that field.
    > >>> >
    > >>> > Up to now, I exported the table to excell, do search&replace there

    and
    > >>> > imported back. This is clumsy and I am afraid there may be some
    > >> corruption
    > >>> > in the table, so I want to be able to do it in access. I am using

    the
    > >> 2003
    > >>> > version.
    > >>> >
    > >>> > How do I do this?
    > >>> >
    > >>> > Regards
    > >>>
    > >>>
    > >>
    > >>

    > >
    > >

    >
    >
     
  10. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    Can't tell you more, because replace is working again
    on the test PC's. Windows and Office have been
    re-installed.

    (david)



    <david@epsomdotcomdotau> wrote in message
    news:egXREFc3FHA.2816@tk2msftngp13.phx.gbl...
    > It was certainly fixed on my computer - and stopped working
    > again sometime after applying SP8. I'm not presently at that
    > computer. I'll try again with 2K2 and 2K3 when I get a chance.
    >
    > (david)
    >
    > "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
    > news:uXAoXGa3FHA.1596@tk2msftngp13.phx.gbl...
    >> And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.
    >>
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in message
    >> news:%23JtqYJU3FHA.732@TK2MSFTNGP10.phx.gbl...
    >> >I know that used to happen with Access 2000, David. But the original

    > poster
    >> >specified Access 2003, and I've never heard of a problem using the
    >> >Replace() function in a query in Access 2003. Are you seeing the problem

    > in
    >> >Access 2003?
    >> >
    >> > --
    >> > Brendan Reynolds
    >> >
    >> > <david@epsomdotcomdotau> wrote in message
    >> > news:%23NLdeAU3FHA.3636@TK2MSFTNGP09.phx.gbl...
    >> >> Unless your computer, like mine, for some obscure
    >> >> reason related to version, installation history, and security
    >> >> patches, refuses to let you use Replace() in a query.
    >> >>
    >> >> In which case you will have to write a VBA function
    >> >> to call replace:
    >> >>
    >> >> Function MyReplace(sSource,sFind,Sreplace) as string
    >> >> MyReplace = replace(sSource,sFind,Sreplace)
    >> >> end function
    >> >>
    >> >> (david)
    >> >>
    >> >> "Brendan Reynolds" <brenreyn@discussions.microsoft.com> wrote in

    > message
    >> >> news:uGQEe3T3FHA.1724@TK2MSFTNGP10.phx.gbl...
    >> >>> You can use the Replace() function in an update query. For example,

    > the
    >> >>> following query will replace all instances of the abbreviation "Ave."

    > in
    >> >> the
    >> >>> Address field in the Employees table with the word "Avenue" ...
    >> >>>
    >> >>> UPDATE Employees SET Employees.Address =
    >> >> Replace([Address],"Ave.","Avenue");
    >> >>>
    >> >>> In query design view, the expression in the "Update to" row looks
    >> >>> like
    >> >> this
    >> >>> ...
    >> >>>
    >> >>> Replace([Address],"Ave.","Avenue")
    >> >>>
    >> >>> --
    >> >>> Brendan Reynolds
    >> >>>
    >> >>> "Veli Izzet" <veli.izzet@gmail.com> wrote in message
    >> >>> news:uzpfFwT3FHA.636@TK2MSFTNGP10.phx.gbl...
    >> >>> > Hi all,
    >> >>> >
    >> >>> > I want to partially update a text field; i.e. changing only a few
    >> >>> > words
    >> >> of
    >> >>> > a sentence in that field.
    >> >>> >
    >> >>> > Up to now, I exported the table to excell, do search&replace there

    > and
    >> >>> > imported back. This is clumsy and I am afraid there may be some
    >> >> corruption
    >> >>> > in the table, so I want to be able to do it in access. I am using

    > the
    >> >> 2003
    >> >>> > version.
    >> >>> >
    >> >>> > How do I do this?
    >> >>> >
    >> >>> > Regards
    >> >>>
    >> >>>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     

Share This Page