Replace using wildcards

[Gordon]

I am trying to update a whole series of records using SQL. The field
is of the format:

ABC3#789\text1
ABC3#625\text2
ABC3#429\text3
etc etc

The first 5 characters of the field are constant.

In effect I want to remove all parts of the field to the left of and
including the "\". I thought of trying to replace that part of the
field with "". Thus I have tried using an update query in SQL:

strsql = "UPDATE tblRegister SET tblRegistert.fldFilename =
Replace(fldFilename,'ABC#3???\','') "
docmd.runSQL strsql

But this does not work.

Can anyone see what I am doing wrong or suggest a better way.

Thanks


Gordon

 

[RoyVidar]

I am trying to update a whole series of records using SQL. The field
is of the format:

ABC3#789\text1
ABC3#625\text2
ABC3#429\text3
etc etc

The first 5 characters of the field are constant.

In effect I want to remove all parts of the field to the left of and
including the "\". I thought of trying to replace that part of the
field with "". Thus I have tried using an update query in SQL:

strsql = "UPDATE tblRegister SET tblRegistert.fldFilename =
Replace(fldFilename,'ABC#3???\','') "
docmd.runSQL strsql

But this does not work.

Can anyone see what I am doing wrong or suggest a better way.

Thanks


Gordon

I don't think the replace function is happy for the wildcards. Here's
another go

strsql = "UPDATE tblRegister SET fldFilename = " & _
"Mid(fldFilename, InStr(fldFilename, '\') + 1)"

If not all fields contain "\", then you'll probably get an error
message, you could add

WHERE Mid(fldFilename, InStr(fldFilename, '\') + 1) > 0

as a where clause, to prevent it.

Note - test on a backup

 

[Arvin Meyer [MVP]]

All of your samples have 14 characters. If that's true of everything, the
answer is simple:

strsql = "UPDATE tblRegister SET tblRegistert.fldFilename =
Right(fldFilename,9)"
docmd.runSQL strsql

 

[Gordon]

Thanks for the suggestions, guys. I had to go with RoyVidar's code
which worked perfectly. I should have explained in my original post
that the text to be left (Text1, Text 2 etc) was of variable length.

Thanks again.

Gordon