Using Caluculated fields to return text

[InsaneJammer]

I have a field where the values are limited to 5 or 6 values, e.g., 162,600,400, etc, but the field data type is text. the numbers are not to be used in calculations, they are just parts of a name.(162S124-18) What I want to do is, in another field, return a different string for each of the values.


I've areadey done this with nested iif functions with no problem, the only difference is in this case I want to return text intead of numerica characters, and it won't except this.
Here is an example of a function that work's:
IIf([Mils]=18,25,IIf([Mils]=27,22,IIf([Mils]=30,20,IIf([Mils]=33,20,IIf([Mils]=43,18,IIf([Mils]=54,16,IIf([Mils]=68,14,IIf([Mils]=97,12,0))))))))

Now If i take this same function and as the true argument,enter, S, or "S", it returns an error. If I enter the true argument as "S", It allowws the expression to go, but whne I save it I get the error.
Does anyone know what I'm doing wrong?

[ajetrumpet]

when you "save it"?? what does that mean?

[InsaneJammer]

I'm in design view and have to save before I can go to data sheet view.

[InsaneJammer]

the erroe message is " cannot accetpt null or binary"

[ajetrumpet]

the erroe message is " cannot accetpt null or binary"

upload the db

[InsaneJammer]

It won't upload, says invalid file, believe it's too big.

[ajetrumpet]

OK. I just don't have time to stay on with this thread. good luck!

[kennejd]

I think I'd use a lookup table. I'm not sure how you pinpoint the characters you're trying to convert, but I would put the values in a table and then add the lookup table to your query. Sorry if that's kind of vague.

Jim

[wes]

It won't upload, says invalid file, believe it's too big.

I can't help with your initial question, but as for uploading, if you make it a zip file it should be small enough to upload. Or you could create a small sample db that only has the pieces you need folks to comment on. Whatever you post should be formatted for an earlier version (maybe Access 2003?) so more people can use it. It really helps people to have something to play with rather than just a description.

For more info check out https://www.accessforums.net/faq.php...b3_attachments