Case / If Calculation isn't working

[Mr. Bungle]

Hi everyone!

 

I have a database that tracks student discipline referrals. I have defined a calculation field (StudentLastFirstTwoLetters) that holds the first 2 letters of a students last name. Who the student counselor is depends on the student's last name, like this:

 

If the student's last name is between a and dd, then the counselor is Moll Flanders

If the student's last name is between de and ke, then the counselor is Tom Thompson

If the student's last name is between kf and q, then the counselor is Jackie Jackson

If the student's last name is between r and z, then the counselor is Carolyn Clark

 

So I created a calc field like this named "CounselorCalc":

 

Case ( StudentLastFirstTwoLetters = "a...dd" ; "Mollie Mason"; StudentLastFirstTwoLetters = "de...ke" ; "Tom Thompson"; StudentLastFirstTwoLetters = "kf...q"; "Jackie Jackson" ; StudentLastFirstTwoLetters = "r...z"; "Carolyn Clark")

 

No errors, but no output either, so I tried it as an 'IF" calculation like this:

 

If ( StudentLastFirstTwoLetters = "a...dd" ; "Mollie Mason"; If (StudentLastFirstTwoLetters = "de...ke" ; "Tom Thompson"; If (StudentLastFirstTwoLetters = "kf...q"; "Jackie Jackson" ; If (StudentLastFirstTwoLetters = "r...z"; "Carolyn Clark"))))

 

Again, no errors, but no output. The field remains stubbornly blank. Can I not specify a range of alpha characters in a calculation field, or should I look at this from an entirely different angle?

 

Thanks in advance--- Mr. B

[LingoJango]

Is the calc output set to text?

 

And I don't think you can use ... in a calc.

[Mr. Bungle]

Yep, set as text.

[AHunter3]

Case ( StudentLastFirstTwoLetters = "a...dd"

 

 

You can't use a range in a calc field like that, as if a calc field were a Find Request. What this is going to be understood as is, literally, that the field StudentLastFirstTwoLetters would have to be the letter "a" followed by three period followed by "dd".

 

 

I assume what you MEANT was, to put it in the vernacular first, "If the field StudentLastFirstTwoLetters contains a value that falls between 'a' and 'dd' " ——— yes?

 

Case (StudentLastFirstTwoLetters ≥ "a" and StudentLastTwoLetters ≤ "dd")

[Mr. Bungle]

You assumed correctly and your suggestion seems to have solved the problem. In addition, I've learned a valuable piece of information I can use in the future.

 

Thank you very much, AHunter3!

 

-Mr. B.