Conditional Update Query

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

Guest

I need to write a query that updates a field, "Fixed" with a segment of a field, "Source." The specific segment of "Source" depends upon a value earlier in the field. If I were writing it as code for text box in a form, I'd probably write something like
If mid([source],56,5)="15464" then [Fixed]=mid([source],102,35
else i
If mid([source],56,5)="15463" then [Fixed]=mid([source],107,28
else i

Can anyone convert this so that I could use it in an update query?
 
Rob said:
I need to write a query that updates a field, "Fixed" with a segment of
a field, "Source." The specific segment of "Source" depends upon a value
earlier in the field. If I were writing it as code for text box in a
form, I'd probably write something like:
If mid([source],56,5)="15464" then [Fixed]=mid([source],102,35)
else if
If mid([source],56,5)="15463" then [Fixed]=mid([source],107,28)
else if

Can anyone convert this so that I could use it in an update query?
Click to expand...
Rob,

If there are only 2 choices, either "15464" or "15463", then:

Update YourTable Set YourTable.Fixed =
IIf(mid([source],56,5)="15464",mid([source],102,35),mid([source],107,28));


If there are more possibilities you can nest IIf statements (like this
one below for 3 choices -- It should all be on one line):

Update YourTable Set YourTable.Fixed =
IIf(mid([source],56,5)="15464",mid([source],102,35),
IIf(mid([source],56,5)="15463",mid([source],107,28),
mid([source],xxx,xx)));

For lots of choices, it would be better to write a User Defined function
placed in a module, using a Select Case statement to make this easier to
read. Then just call that function from the query using:
.... Set YourTable.Fixed = YourFunction([Source])

That's another post, however.
 
Back
Top