Calculate new value from existing value?

  • Thread starter Thread starter Elizabeth Swoope
  • Start date Start date
E

Elizabeth Swoope

I need to categorize entries based on the value of a particular field. It
doesn't matter to me whether the new category field is in a query ("best
practices") or whether I write a little code to calculate and fill the new
field as data is entered into the form.

The data goes in as a text field consisting of four digits. Shown below are
the input data and the desired new field value:

1110 -> 1110
1120 -> 1120
1220 -> 1220
1230 -> 1230
1240 -> 1240
37xx -> 3700 (that is, any code beginning with 37 translates to 3700)

everything else become the first digit of the code followed by three zeroes
(e.g., 4235 -> 4000)

If I had only the 37xx and everything else, that's an easy IIF. I've looked
at switch and choose and they don't seem quite right. If I do this in a
query, it appears that I have to create a hellacious statement with a bunch
of nested IIFs.

I hope that I'm missing something very simple here! I can muddle through
writing code to calculate this in a form but I'd rather use a query if
possible.

Any input or advice is appreciated.

Thanks,

liz
 
Marsh,

Thanks for your quick response!

I wasn't clear with the choices, but I was able to modify the code you
posted by removing the < "3700" part and adding each of the five individual
codes. There may be some other codes that start with 1 or 2 or 3 (but not 37)
that conform to the x000 format.

What I was missing was that last "true" section at the end. I knew
theoretically that I wanted a final "else" case but didn't know how to do it.
Your example showed me.

It's a gnarly formula, to be sure, but it works!

ExpObj: Switch(Left([ObjCode],2)="37","3700", _
[ObjCode]="1110","1110",[ObjCode]="1120","1120", _
[ObjCode]="1220","1220",[ObjCode]="1230","1230", _
[ObjCode]="1240","1240",True,Left([ObjCode],1) & "000")

liz
 
Marsh,

I'm familiar with the concept of "in" from programming in SAS and was
wondering if Access had a function like that. I should have looked harder!

If I spent more time with Access, I'd pick up this stuff. Unfortunately, I
barely scratch the surface. I really do like Access 2007 and working with it
give me the opportunity to learn lots of new-to-me neat things.

Thanks for taking the time to solve this problem for me. You and the other
MVPs are great. Whenever I get stuck with something, I know that I can post a
message in the appropriate area, and someone will be willing and able to help
me.

liz
 
Marsh,

Thanks for the clarification. I have several Access and VBA books, but there
is so much to learn about Access that trying to find the information I need
is tough.

I probably shouldn't have used the term "function" since that has a specific
meaning. I should have said "capability" or used a term that doesn't have a
specific meaning in many computer programs, I suppose. I definitely
appreciate the clarification about IN being an operator, and thus only
available in queries and the like but not in VBA.

Anyway, your help is much appreciated!

liz
 
Back
Top