Calculate new value from existing value?

[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

 

[Elizabeth Swoope]

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

 

[Elizabeth Swoope]

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

 

[Elizabeth Swoope]

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