IIF Statements in update query

[Guest]

I created an update query with the following IIF statement in the "update to"
line for the field "Region" which currently has no data.

IIF([Zone],1,"Downtown", IIF([Zone],2,"South Central",
IIF([Zone]=3,"Eastern", IIF([Zone]=4,"North", IIF([Zone]=5,"Western",
IIF([Zone]=6,"Southern", IIF([Zone]=7,"North Central","Not Specified")))))))

I get the error: "The expression you entered has a function containing the
wrong number of arguments."

Can someone please point out my mistake?

 

[Stefan Hoffmann]

hi Tina,

IIF([Zone],1,"Downtown", IIF([Zone],2,"South Central",

Shouldn't that be [Zone]=1?

IIF([Zone]=3,"Eastern", IIF([Zone]=4,"North", IIF([Zone]=5,"Western",
IIF([Zone]=6,"Southern", IIF([Zone]=7,"North Central","Not Specified")))))))

Do you have a table defining the zones? When not, than just create on
consisting of two columns ID, integer, primary key and Caption, string,
not null:

ID, Caption
1, "Downtown"
2, "South Central"
3, "Eastern"
....

Then you can use a join instead of your IIf construct.

btw, what city are we tlaking about, L.A.?


mfG
--> stefan <--

 

[Guest]

LOL. You are generous in your response. Yes, it should include an = sign,
but it's Monday and I didn't get much sleep last night. I can't tell you how
many times I looked at the SQL and didn't catch it! Geez....

I do have a table defining the zones. It's a lookup table and I didn't want
to have to type in the region names. The table has the PIN number and the
Zone the PIN number is located in. I wanted to get more descriptive for the
user since the Zone numbers don't mean anything, but the region names do. I
didn't want to have to type this in for the 287 PINs.

I work for Wake County Human Services in North Carolina - Raleigh makes up
most of the county. Nothing so grand as LA.

Have a great week.
--
Thanks,
Tina Hudson

hi Tina,

IIF([Zone],1,"Downtown", IIF([Zone],2,"South Central",

Shouldn't that be [Zone]=1?

IIF([Zone]=3,"Eastern", IIF([Zone]=4,"North", IIF([Zone]=5,"Western",
IIF([Zone]=6,"Southern", IIF([Zone]=7,"North Central","Not Specified")))))))

Do you have a table defining the zones? When not, than just create on
consisting of two columns ID, integer, primary key and Caption, string,
not null:

ID, Caption
1, "Downtown"
2, "South Central"
3, "Eastern"
....

Then you can use a join instead of your IIf construct.

btw, what city are we tlaking about, L.A.?


mfG
--> stefan <--

 

[John Spencer]

Well, you could add a Region table with the RegionNames and the ZoneNumbers.
That should only require 7 records since you were only concerned with 7
zones.

Then an outer join (left or right as appropriate) between the PIN table and
the new Region table would either return a value or a null which you could
force to display "Not Specified" using the NZ function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

LOL. You are generous in your response. Yes, it should include an =
sign,
but it's Monday and I didn't get much sleep last night. I can't tell you
how
many times I looked at the SQL and didn't catch it! Geez....

I do have a table defining the zones. It's a lookup table and I didn't
want
to have to type in the region names. The table has the PIN number and the
Zone the PIN number is located in. I wanted to get more descriptive for
the
user since the Zone numbers don't mean anything, but the region names do.
I
didn't want to have to type this in for the 287 PINs.

I work for Wake County Human Services in North Carolina - Raleigh makes
up
most of the county. Nothing so grand as LA.

Have a great week.
--
Thanks,
Tina Hudson

hi Tina,

IIF([Zone],1,"Downtown", IIF([Zone],2,"South Central",

Shouldn't that be [Zone]=1?

IIF([Zone]=3,"Eastern", IIF([Zone]=4,"North", IIF([Zone]=5,"Western",
IIF([Zone]=6,"Southern", IIF([Zone]=7,"North Central","Not
Specified")))))))

Do you have a table defining the zones? When not, than just create on
consisting of two columns ID, integer, primary key and Caption, string,
not null:

ID, Caption
1, "Downtown"
2, "South Central"
3, "Eastern"
....

Then you can use a join instead of your IIf construct.

btw, what city are we tlaking about, L.A.?


mfG
--> stefan <--

 

[Guest]

John,

I like that! Although I know have another question, if you don't mind!
Which method (yours or Stefan's) would be best to use if we change the makeup
of the regions every year, in addition to adding an 8th region soon? We will
always have the same PIN numbers, just different PINS will be assigned to
different regions. Although I don't think it would be a sweeping change,
just a subtle change.

--
Thanks,
Tina Hudson

Well, you could add a Region table with the RegionNames and the ZoneNumbers.
That should only require 7 records since you were only concerned with 7
zones.

Then an outer join (left or right as appropriate) between the PIN table and
the new Region table would either return a value or a null which you could
force to display "Not Specified" using the NZ function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

LOL. You are generous in your response. Yes, it should include an =
sign,
but it's Monday and I didn't get much sleep last night. I can't tell you
how
many times I looked at the SQL and didn't catch it! Geez....

I do have a table defining the zones. It's a lookup table and I didn't
want
to have to type in the region names. The table has the PIN number and the
Zone the PIN number is located in. I wanted to get more descriptive for
the
user since the Zone numbers don't mean anything, but the region names do.
I
didn't want to have to type this in for the 287 PINs.

I work for Wake County Human Services in North Carolina - Raleigh makes
up
most of the county. Nothing so grand as LA.

Have a great week.
--
Thanks,
Tina Hudson

hi Tina,

Tina Hudson wrote:
IIF([Zone],1,"Downtown", IIF([Zone],2,"South Central",
Shouldn't that be [Zone]=1?

IIF([Zone]=3,"Eastern", IIF([Zone]=4,"North", IIF([Zone]=5,"Western",
IIF([Zone]=6,"Southern", IIF([Zone]=7,"North Central","Not
Specified")))))))

Do you have a table defining the zones? When not, than just create on
consisting of two columns ID, integer, primary key and Caption, string,
not null:

ID, Caption
1, "Downtown"
2, "South Central"
3, "Eastern"
....

Then you can use a join instead of your IIf construct.

btw, what city are we tlaking about, L.A.?


mfG
--> stefan <--

 

[John Spencer]

I really don't know which option would be better. It depends on how you
want things to work. You may need to store effective date ranges if your
historic data and new data are not in agreement.

I don't know enough about your data structure, business rules, purpose of
the data, etc to advise you further on the best choice. I can only say that
you do have options better than hard-coding complex statements into your
queries that will have to be edited when data and the relationships for the
data change.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..