Changing text in a field during a qry update

  • Thread starter Thread starter Mitch
  • Start date Start date
M

Mitch

Hi all, i'd like to create a update query that does this
in Excel....

Range("K:K").Replace What:="Ltr/C",
Replacement:="Letter", _
SearchOrder:=xlByColumns, MatchCase:=False

....although I can already do this in Excel i'd like to be
able to do this in Access200o. From what I understand I
have to use an update query but i'm not sure how to use
IIF in the update field in the query. The value in the
field I want to update could look like "xxxltr/cxx"
or "xltr/cxxxxx" but I want to update it to read Letter
or if it reads "xxldgrxxx" I want to change it to
read "Ledger" so the qwuery has to look for certain text
and change it to the value depending on if it finds that
specific text. I hope that makes sense !!
Thanks in advance,
Mitch....
 
Something like this, perhaps?

UPDATE TableName
SET FieldName = "Letter"
WHERE FieldName = "Ltr/C";
 
...
Something like this, perhaps?

UPDATE TableName
SET FieldName = "Letter"
WHERE FieldName = "Ltr/C";
Click to expand...

Something more like this:

UPDATE
[Excel 8.0;HDR=No;Database=C:\MyWorkbook].[MySheet$K:K]
SET
F1 = Mid(F1, 1, InStr(1, F1, 'ltr/c', 1) - 1)
& 'Letter'
& Mid(F1, InStr(1, F1, 'ltr/c', 1) + 5)
WHERE
InStr(1, F1, 'ltr/c', 1) > 0
;

But note this isn't recursive e.g. xLtr/CxxLtr/Cxx would become
xLetterxxLtr/Cxx.

Although you may be able to get away with nested IIFs, I'd recommend
separate queries of each of Letter, Ledger, etc.

Jamie.

--
 
...
Something like this, perhaps?

UPDATE TableName
SET FieldName = "Letter"
WHERE FieldName = "Ltr/C";
Click to expand...

Something more like this:

UPDATE
[Excel 8.0;HDR=No;Database=C:\MyWorkbook].[MySheet$K:K]
SET
F1 = Mid(F1, 1, InStr(1, F1, 'ltr/c', 1) - 1)
& 'Letter'
& Mid(F1, InStr(1, F1, 'ltr/c', 1) + 5)
WHERE
InStr(1, F1, 'ltr/c', 1) > 0
;

But note this isn't recursive e.g. xLtr/CxxLtr/Cxx would become
xLetterxxLtr/Cxx.

Although you may be able to get away with nested IIFs, I'd recommend
separate queries of each of Letter, Ledger, etc.

Jamie.

--
 
...
Something like this, perhaps?

UPDATE TableName
SET FieldName = "Letter"
WHERE FieldName = "Ltr/C";
Click to expand...

Something more like this:

UPDATE
[Excel 8.0;HDR=No;Database=C:\MyWorkbook].[MySheet$K:K]
SET
F1 = Mid(F1, 1, InStr(1, F1, 'ltr/c', 1) - 1)
& 'Letter'
& Mid(F1, InStr(1, F1, 'ltr/c', 1) + 5)
WHERE
InStr(1, F1, 'ltr/c', 1) > 0
;

But note this isn't recursive e.g. xLtr/CxxLtr/Cxx would become
xLetterxxLtr/Cxx.

Although you may be able to get away with nested IIFs, I'd recommend
separate queries of each of Letter, Ledger, etc.

Jamie.

--
 
Almost.. .problem is that the field may contain other
text in the value as well as Ltr/c... so it may have
xxxltr/c or whatever, so the qry needs to find if ltr/c
is in the value of the field before updating.
Thanks for your input.
 
Almost.. .problem is that the field may contain other
text in the value as well as Ltr/c... so it may have
xxxltr/c or whatever, so the qry needs to find if ltr/c
is in the value of the field before updating.
Click to expand...

Change the criterion from

= "ltr/c"

to

LIKE "*ltr/c*"

and it will replace the entirity of the field (discarding all the
xxx's) with Letter, if the text string ltr/c appears anywhere within
it.
 
John Vinson wrote ...
Change the criterion from

= "ltr/c"

to

LIKE "*ltr/c*"

and it will replace the entirity of the field (discarding all the
xxx's) with Letter, if the text string ltr/c appears anywhere within
it.
Click to expand...

Combining the best of our replies:

UPDATE
[Excel 8.0;HDR=No;Database=C:\MyWorkbook].[MySheet$K:K]
SET
F1 = Mid(F1, 1, InStr(1, F1, 'ltr/c', 1) - 1)
& 'Letter'
& Mid(F1, InStr(1, F1, 'ltr/c', 1) + 5)
WHERE
F1 LIKE "%ltr/c%"
;

Jamie.

--
 
Back
Top