for each rec: replace val in 1 field w/ val from other field

[tchristo]

Hi,

I have a field with the same string value....

(a hyperlink actually, with this as the string:
&quot;<a href= ../asp/query_cover.asp?WSD_size=6&WSD_code=020503 target=_blank><b>Run Query/View Chart</b></a>&quot

....copied to each record. For each record, I need to replace the part &quot;020503&quot; with the string found in the &quot;ID&quot; field (So the replacement is the unique record id).

So I can't do a simple, update query because only part of the string is being replaced not all.

Can someone help with either figuring out a modification to an update sql or a vba loop through the records that will help accomplish this.

Thanks in advance,
Treg

 

[TonyJollans]

Hi tregc,

You can do this with an update query, providing you can identify what to replace.

If your string is always the same then you can use ..

UPDATE [

TableName

] SET [

TableName

].[

FieldName

] = &quot;<a href= ../asp/query_cover.asp?WSD_size=6&WSD_code=&quot; & [

TableName

].[

IDFieldName

] & &quot; target=_blank><b>Run Query/View Chart</b></a>&quot;;

If your string varies you must identify the two halves somehow. This assumes it will always break at WSD_code= and that the number os always 6 digits long ..

UPDATE [

TableName

] SET [

TableName

].[

FieldName

] = Left([

TableName

].[

FieldName

],InStr([

TableName

].[

FieldName

],"WSD_code=")+8)& [

TableName

].[

IDFieldName

] & Mid([

TableName

].[

FieldName

],InStr([

TableName

].[

FieldName

],"WSD_code=")+15);

If neither of these suits and you can't take it further yourself then post back with what the criteria are and I'll adapt accordingly.

Enjoy,
Tony

 

[tchristo]

Tony,

Your first example worked great!

Thanks a bunch,
Treg