VBA ~ Searching & Replaccing text in a field

bigmantone · #1 (**permalink**) 06-05-04, 10:32

Hi All,

Please can you help.

I am trying to create VBA code that can search each record in a particular field of a recordset and replace it?

The entrys look like this

12456.gif
13456.gif
12789.gif
78945.jpg
79654.jpp

What I want to do is find .gif and replace it to .jpg

Hers my code so far, as you can see I can get into the recordset.

Any help would be great

Function update()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Table1")

Do While Not rec.EOF
rec.Edit
rec("Sale") = [Text21].Value
rec("Catapic") = [Text23].Value
If rec("Photo name") = "Noimage" Then rec("Photo name") = "Noimage.jpg"
If rec("Photo thumb_name") = "Noimage" Then rec("Photo thumb_name") = "Noimage.jpg"
If rec("Photo name") = ".GIF" Then rec("Photo name") = ".jpg" ' this is where I am stuck

rec.update
rec.MoveNext

Loop

update = True

End Functio

CyberLynx · #2 (**permalink**) 06-05-04, 22:16

This routine will change the Photo Name "MyPicture.GIF" to "MyPicture.jpg"

Code:

If Instr(rec.[Photo name], ".GIF") = True Then 
   rec.[Photo name] = Left(rec.[Photo name], Instr(rec.[Photo name], ".GIF") - 1) & ".jpg" 
End If

OR cleaner yet....

Code:

Dim Strg As String
Strg = rec.[Photo name]
If Instr(Strg, ".GIF") = True Then 
   rec.[Photo name] = Left(strg, Instr(Strg, ".GIF") - 1) & ".jpg" 
End If

Internet Services

Web Development

Digital Marketing

Consumer Tech