IF statement needed for a query

[JOEHAR]

If "Name" starts with "Dav" then replace it with corresponding name in "Replace Name"

Name	Replace Name
Sam	Samuel
Dave	David
Rick	Richard
Ed	Edward

Thanks!

[Micron]

Only try suggested code/sql on table copies. Try

UPDATE tblYourTable SET tblYourTable.Name = tblYourTable.ReplaceName 
WHERE Left(tblYourTable.Name,3) = "Dav";

You did NOT name your field "Name", right?

[ssanfu]

What is the context of this? Do you have a main table that you want to change the "nick name" to the real first name using a small table with the cross reference?

I think you will need to use VBA to get through all of the names or else the criteria will be huge. You will need to explicitly enumerate the "names" in the WHERE clause"

UPDATE tblYourTable SET tblYourTable.Name = tblYourTable.ReplaceName 
WHERE Left(tblYourTable.Name,3) = "Dav" Or
      Left(tblYourTable.Name,3) = "Sam" Or
      Left(tblYourTable.Name,3) = "Ric" Or
      Left(tblYourTable.Name,3) = "Ed" Or ...

[CJ_London]

I can see lots of potential issues

what if Sam can also stand for Samantha, or Ed stands for Edwin or Edgar?

and if the name is already David?

Simplistically you can link, something along these lines

SELECT FirstName, ReplaceName
FROM tblNames INNER JOIN tblReplacements ON tblNames.FirstName=tblReplacements.Name

OR

SELECT FirstName, ReplaceName
FROM tblNames, tblReplacements
WHERE tblNames.FirstName LIKE tblReplacements.Name & "*"

Overall you have not set yourself an easy task

[Micron]

I can see lots of potential issues

Me too. All good comments guys; I just figured that after my answer, we'd find out that there was a lot more to it. I just could not imagine using a variation of Left on every possible value in a field.

[CJ_London]

you need to have the replacement table - I have a standard one I use where typically one 'master' phrase can have many alternatives. (Master=Edward, Aliases=Ed, Eddie)

tblPhrases
PhrasePK, PhraseTypeFK, Phrase, MasterFK

the 'replacement' is the record where PhrasePK=MasterFK, Phrase and PhraseTypeFK has a compound index, no dups

PhraseTypeFK - if I need say aliases for employee names and products or perhaps based on language or different offices - identified in a phraseType table

Phrases can be a single letter (used in translation), a single word or a whole sentence



you can also use non standard joins rather than a cartesian query - the example sql in my previous post could be combined to look like

SELECT FirstName, ReplaceName
FROM tblNames INNER JOIN tblReplacements ON tblNames.FirstName LIKE tblReplacements.Name & "*"