Update Query

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

Guest

I would like use an update query to change data for a field in a table based
on criteria. If the criteria = A, update to B. If the criteria = C, update
to D. How can I do this with a single query?
 
Normally I'd say that you can't; HOWEVER, if you are talking about uppercase
A updating to uppercase B and C to D it is possible with a little tomfoolery.

Make a backup of the table or entire database first.....

UPDATE tblAlphabet
SET tblAlphabet.AlphaCharacter = Chr(Asc([AlphaCharacter])+1)
WHERE (((Asc([AlphaCharacter]))=65 Or (Asc([AlphaCharacter]))=67));

A is ASCII character 65 and C is 67. Adding one to them and converting them
back to characters will give you B and D. Lowercase are different numbers.

I took what you said extremely literally. Any differences, like B to G and
it doesn't work.

That was fun! (I need to get out more.....)
 
Actually, the letters were just examples, not what I wanted to update the data.

Jerry Whittle said:
Normally I'd say that you can't; HOWEVER, if you are talking about uppercase
A updating to uppercase B and C to D it is possible with a little tomfoolery.

Make a backup of the table or entire database first.....

UPDATE tblAlphabet
SET tblAlphabet.AlphaCharacter = Chr(Asc([AlphaCharacter])+1)
WHERE (((Asc([AlphaCharacter]))=65 Or (Asc([AlphaCharacter]))=67));

A is ASCII character 65 and C is 67. Adding one to them and converting them
back to characters will give you B and D. Lowercase are different numbers.

I took what you said extremely literally. Any differences, like B to G and
it doesn't work.

That was fun! (I need to get out more.....)

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Karen said:
I would like use an update query to change data for a field in a table based
on criteria. If the criteria = A, update to B. If the criteria = C, update
to D. How can I do this with a single query?
Click to expand...
Click to expand...
 
I would like use an update query to change data for a field in a table based
on criteria. If the criteria = A, update to B. If the criteria = C, update
to D. How can I do this with a single query?
Click to expand...

You'll need to use the Switch() function, or some nested IIF()
functions. Your question is vague, but for a vague answer - update
TargetField to

Switch([Criteria] = "A", "B",
[Criteria] = "C", "D",
True, [TargetField])

The Switch() function takes arguments in pairs, and returns the second
member of the first pair for which the first member of the pair is
true; so this will update the target field to "B" or "D" if the
criteria match, and update it to its current value (i.e. don't change
it) if neither criterion holds.

John W. Vinson[MVP]
 
Back
Top