Update Query help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having trouble with creating an Update Query.

I'm trying to find where De is in the query and then change it to de.

The values in the field are like this

Santos De Lama
Garvoo De sun
El De Vi
etc...

so after the update has run
it would be

Santos de Lama
Garvo de sun
El de Vi
etc...

so I will only be updating the de and nothing else.
I know how to limit the criteria part to find where De is Like "*De*"
but do not know what to use in the Update To:

Thanks
David
 
Use the InStr() function, as in InStr(FieldName, "De"). Look up the function
in the help file for details.

HTH
 
I'm having trouble with creating an Update Query.

I'm trying to find where De is in the query and then change it to de.

The values in the field are like this

Santos De Lama
Garvoo De sun
El De Vi
etc...

so after the update has run
it would be

Santos de Lama
Garvo de sun
El de Vi
etc...
Click to expand...

This is a little tricky, because you don't want to change the "De" in
"Deborah Jones" or "George Dennison"...

Try a criterion on the field of

LIKE "* de *"

to select only records with an isolated word "de" (the search is non
case sensitive so it will find de, De, or DE).

Then run an Update query updating the field (which I'll call
Namefield) to

Left([Namefield], InStr([namefield], " de ") - 1) & " de " &
Mid([Namefield], InStr([namefield], " de ") + 4)

to splice together the portion before " de ", a literal text string "
de ", and then the portion after.

John W. Vinson[MVP]
 
Back
Top