Re: newbie - sql UPDATE statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 02/22/05 

Date: Wed, 23 Feb 2005 08:09:12 +1100

DoCmd.RunSQL "UPDATE [Eligibility] SET [YourField] = " & _
" Replace([YourField], '0301730', '30173C0') " & _
" WHERE [YourField] LIKE '*0301730'", True

Replace [YouField] with actual Field name. You seemed to refer to the same
Field by 2 different names, namely [Data] and [Group] so I am not sure which
one is the correct Field name. 

-- 
HTH
Van T. Dinh
MVP (Access)
"Newbee Adam" <NewbeeAdam@discussions.microsoft.com> wrote in message
news:05E7BE56-3A8E-4D07-89CF-5228838473F7@microsoft.com...
> I am running this command in an access 2000 code module. I am trying to
> update a table in the same access 2000 db. I need to update this part of
the
> field value: 0301730" to 030173C0. But the first part of the string :
> 2040018000 works like a variable. I need to just update part of the
string,
> this part : 0301730
> basically I need to insert the letter "C" into 0301730" after the 3""
>
>
>
> DoCmd.RunSQL "UPDATE [Eligibility] SET [Data] = '" & "2040018000
030173C0"
> & "' WHERE [Group] = '" & "2040018000  0301730" & "';", True
>
> I do think a replace function can be used in an sql statement like I use
> outside an sql string like:
>
> replace("0301730","730","73C0")
> DoCmd.RunSQL "UPDATE [Eligibility] SET [Data] = '" &
> replace("0301730","730","73C0") & "' WHERE [Group] = '" & "2040018000
> 0301730" & "';", True
>
>
> can any one help
> -- 
> Adam S

Relevant Pages

  • Re: Using VBA to create a Table in Access database
    ... "Set rst = qdf.OpenRecordset" ... in data which forms the WHERE clause of the sql statement. ... i declared qdf.sql = sqlstring followed by ... If you want to modify the code so that you pass in a simple SQL string, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Nested Datalists
    ... This SQL statement returns all the information that I need (Drawing #, ... (MaxOfDCN) ... this SQL string as the DataSource for one of my datalists. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: How can i get the "true" name of an aliased field?
    ... I'm opening an ADO recordset with an SQL string which goes something ... Use rs.Source to get the sql statement that was used to populate the ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Single Quote in SQL Statement
    ... it is easier to debug in you can construct the SQL String separately ... Dim strSQL As String ... > I need to run a insert sql statement into one of my ... the sub get arguments that need to ...
    (microsoft.public.access.modulesdaovba)