newbie - sql UPDATE statement

22 Feb 2005 7:33 PM

Newbee Adam

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

22 Feb 2005 8:55 PM

anonymous

DoCmd.RunSQL "UPDATE [Eligibility] SET elegibility.Data
= "2040018000 030173C0"
WHERE (((group)= "criteria"));
criteria would identfy the exact record to update.

>-----Original Message-----
>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
Show quote

>0301730" & "';", True
>
>
>can any one help
>--
>Adam S
>.
>

22 Feb 2005 9:09 PM

Van T. Dinh

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)

Show quote

"Newbee Adam" <NewbeeA***@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