Update Query to change text?

[Guest]

Hi, Working on a Query in the MOS exam, I was asked for a single command that
would change "De" to "de". I don't remember if I eventually did a find &
replace or not.

But: I thought maybe I should have done an Update Query, so, teaching
myself, I decided to try change any occurrence of the text "Ad" as in "The
Adventures of Huck Finn" to "ad". I tried this:
Criteria: Like "*Ad*"
Update To: "*ad*"
Result: All matching record titles changed to *ad* (not The adventures...)
Question: Is there a way to change text using a query, or should I forget it
and concentrate on Find/Replace?
(Failed the exam badly even though I knew the book and the DDC sample tests,
so am now wasting days and nights trying to find answers that the book
doesn't tell me...) Oh for a mentor..
Also in exam I couldn't expand a footer in one form, then couldn't close a
form when asked, so I'm not very confident going into the retake. Anybody
with same problems?

 

[John Vinson]

Hi, Working on a Query in the MOS exam, I was asked for a single command that
would change "De" to "de". I don't remember if I eventually did a find &
replace or not.

The Replace() VBA function would be one way to do this.

But: I thought maybe I should have done an Update Query, so, teaching
myself, I decided to try change any occurrence of the text "Ad" as in "The
Adventures of Huck Finn" to "ad". I tried this:
Criteria: Like "*Ad*"
Update To: "*ad*"
Result: All matching record titles changed to *ad* (not The adventures...)

Exactly.

The criterion found all records containing the text string "ad" (or
"AD", or "Ad", or "aD" - Access searches are NOT case sensitive) in
that field. It returned a "recordset" of all the records in the table
which met that criterion.

The Update To clause of your query told Access that you wanted the
field updated to the text string "*ad*". It did what you asked it to
do.

Question: Is there a way to change text using a query, or should I forget it
and concentrate on Find/Replace?

Find/Replace on the table view window is simply a relatively
user-friendly way of running a Query. It'll have the same problem!

To change the case of a single letter embedded anywhere within an
arbitrary text string isn't all that easy - a simple Update Query (as
you found!!) will NOT do the trick. The Replace() function (type
Ctrl-G to open the VBA editor and look for help on it) will work as
part of an update query.

(Failed the exam badly even though I knew the book and the DDC sample tests,
so am now wasting days and nights trying to find answers that the book
doesn't tell me...) Oh for a mentor..
Also in exam I couldn't expand a footer in one form, then couldn't close a
form when asked, so I'm not very confident going into the retake. Anybody
with same problems?

I have had similar problems in the past; classical thermodynamics at
Michigan State in 1966 comes to mind... <g>

Book learning is NO substitute for opening a database, trying things,
and making very liberal use of the F1 key. Good luck...

John W. Vinson[MVP]