Auto populate field by Iif statement?

[Guest]

I have a field with an ID number. From this number I can tell which of 4
offices it originated from. (IDs from one office start with an "s", IDs from
another all start with an "n", etc.)
Is there a way to automatically populate an "Office" field with what office
it came from, based on the first letter of the "ID" field?

Thanks! in advance.

 

[tina]

IIf(Left(IDFieldName, 1) = "s", "OfficeS", IIf(Left(IDFieldName, 1) = "n",
"OfficeN", IIf(Left(IDFieldName, 1) = "x", "OfficeX", IIf(Left(IDFieldName,
1) = "y", "OfficeY", ""))))

hth

 

[Guest]

This looks awesome, now where do I put it? Would this be in a query, or can I
make it an actual field in a table? (Sorry for the ignorance!)

 

[tina]

you can't use an expression in a table, but you can use it to create a
"calculated" field in a query. just enter the expression in the Field: line
in a blank column in a query that includes the ID number field.

hth

 

[Mike Painter]

This looks awesome, now where do I put it? Would this be in a query,
or can I make it an actual field in a table? (Sorry for the
ignorance!)

Another way would be to have a table of offices with a key equal to the
letter you want.
Create a query based on the tables containing the IFField name with a
calculated field
LEFTID:Left(IDFieldName, 1)
Buid another query relating this calculated field to the office table.
That way you can add offices and not have to add to your IIf everyplace.

 

[Guest]

Thank you. This is perfect.

Another way would be to have a table of offices with a key equal to the
letter you want.
Create a query based on the tables containing the IFField name with a
calculated field
LEFTID:Left(IDFieldName, 1)
Buid another query relating this calculated field to the office table.
That way you can add offices and not have to add to your IIf everyplace.