Auto populate field by Iif statement?

N

Newby db designer

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.
 
T

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
 
N

Newby db designer

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!)
 
T

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
 
M

Mike Painter

Newby said:
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!)
Click to expand...
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.
 
N

Newby db designer

Thank you. This is perfect.

Mike Painter said:
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.
Click to expand...
 
Top