Else If Statements

[Guest]

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then Access
says the expression is too complicated. What else can I use so I can do more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of more
than 13?

I hope I have explained enough, please let me know if you need anymore info.

Thanks

Paul

 

[Guest]

A different approach would be to build a reference table with two fields -
"Lookup" and "Location."

In your query add the reference table, not joined, and use the Lookup field
as criteria for [X] and the Location as Output.

 

[Amy Blankenship]

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

 

[Guest]

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what you
are trying to do but am unsure what the calculated field code would be? I
can obviously get the reference table into the query but how would I then say
"if that equals that then equal this? Can you please provide me with some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

A different approach would be to build a reference table with two fields -
"Lookup" and "Location."

In your query add the reference table, not joined, and use the Lookup field
as criteria for [X] and the Location as Output.

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then Access
says the expression is too complicated. What else can I use so I can do more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of more
than 13?

I hope I have explained enough, please let me know if you need anymore info.

Thanks

Paul

 

[Guest]

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what you
are trying to do but am unsure what the calculated field code would be? I
can obviously get the reference table into the query but how would I then say
"if that equals that then equal this? Can you please provide me with some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from
within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then
Access
says the expression is too complicated. What else can I use so I can do
more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of
more
than 13?

I hope I have explained enough, please let me know if you need anymore
info.

Thanks

Paul

 

[Amy Blankenship]

To be more specific with you, you have to be more specific with us. So what
is the structure of the table you are querying, or even the text of the
existing query? It may be that you can just join to the lookup table, as
Karl suggested.

HTH;

Amy

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what you
are trying to do but am unsure what the calculated field code would be? I
can obviously get the reference table into the query but how would I then
say
"if that equals that then equal this? Can you please provide me with some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

Hi,

I am currently running a query off a table in Access. I have a
calculated
field in the query which looks at the results of another column from
within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then
Access
says the expression is too complicated. What else can I use so I can
do
more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of
more
than 13?

I hope I have explained enough, please let me know if you need anymore
info.

Thanks

Paul

 

[Guest]

Create a table that has the code and location for all that you may expect to
have to find. Add this table to your existing query. Instead using the
calculated field, join the code in the new table the field you are using in
you calculated field [x]
This is the fastest and easiest way to do it.

 

[Guest]

Hi,

I started with a table with about 8 columns in it and then pulled a query
together off that table with calculated fields in the query to do some
analysis of the source data.

Within the origional table there is a post code field. I striped the
postcode down to only show the first part of the postcode i.e. the alpha
part. What I need to do then is to put another calculated field into the
query to show where that post code belongs to, for e.g. WF = Wakefield, LS =
Leeds etc. First I thought I could do this with "IIf([Post
Code]="WF","Wakefield","Unknown")" but then found that you could only have 13
else IIf statements which is no where near enough.

I have another table that shows where each postcode belongs to but I don't
know how to do a lookup like you use a VLOOKUP in excel.

There are 2 tables now in my query, not joined. One called Post Codes, i.e.
the reference table and the origional source data table. The Post Code table
contains 132 possible post code areas. I have put the following code into
the table:-

Test: IIf([Actual Post Code]=[UK Post Codes]![Post Code],[UK Post
Codes]![City/Town],"Unknown")

This works but the problem is it produces 132 rows per line of the origional
source data table? How do I combat this so it only finds the code I want and
produces just one results for each line of the origional source data??

Hope this is a bit more specific?

Thanks

Paul

To be more specific with you, you have to be more specific with us. So what
is the structure of the table you are querying, or even the text of the
existing query? It may be that you can just join to the lookup table, as
Karl suggested.

HTH;

Amy

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what you
are trying to do but am unsure what the calculated field code would be? I
can obviously get the reference table into the query but how would I then
say
"if that equals that then equal this? Can you please provide me with some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

Hi,

I am currently running a query off a table in Access. I have a
calculated
field in the query which looks at the results of another column from
within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then
Access
says the expression is too complicated. What else can I use so I can
do
more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of
more
than 13?

I hope I have explained enough, please let me know if you need anymore
info.

Thanks

Paul

 

[Guest]

This would be fine but [X] is already a calculated field and not a field in a
table.

To do what you say below I would have to make the query into a make table
query and then do another query which I don't want to do if I can help it?

Anymore suggestions.

Thanks

Paul

Create a table that has the code and location for all that you may expect to
have to find. Add this table to your existing query. Instead using the
calculated field, join the code in the new table the field you are using in
you calculated field [x]
This is the fastest and easiest way to do it.

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then Access
says the expression is too complicated. What else can I use so I can do more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of more
than 13?

I hope I have explained enough, please let me know if you need anymore info.

Thanks

Paul

 

[Phil]

Create a table with your info in it, what to look for, what to return.
In you case, postal code, (or partial) and name of city it goes with.

Use DLOOKUP on that table to look up the city you are looking for. You
could use a single IIF to return "Unknown", if Dlookup fails, by testing
to see if your Clookup returns a null.

If you can't figure it out, build the table, and give us the name of the
table, the two field names in that table, and the calculation for [X],
and we can work it out for you.

This would be fine but [X] is already a calculated field and not a field in a
table.

To do what you say below I would have to make the query into a make table
query and then do another query which I don't want to do if I can help it?

Anymore suggestions.

Thanks

Paul

:

Create a table that has the code and location for all that you may expect to
have to find. Add this table to your existing query. Instead using the
calculated field, join the code in the new table the field you are using in
you calculated field [x]
This is the fastest and easiest way to do it.

:

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then Access
says the expression is too complicated. What else can I use so I can do more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of more
than 13?

I hope I have explained enough, please let me know if you need anymore info.

Thanks

Paul

 

[Guest]

No code needed. Open the query in design view to do what I posted.

Or you can do the same as "Klatuu" posted in design view. No need to do a
make table.

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what you
are trying to do but am unsure what the calculated field code would be? I
can obviously get the reference table into the query but how would I then say
"if that equals that then equal this? Can you please provide me with some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

A different approach would be to build a reference table with two fields -
"Lookup" and "Location."

In your query add the reference table, not joined, and use the Lookup field
as criteria for [X] and the Location as Output.

Hi,

I am currently running a query off a table in Access. I have a calculated
field in the query which looks at the results of another column from within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements, then Access
says the expression is too complicated. What else can I use so I can do more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias of more
than 13?

I hope I have explained enough, please let me know if you need anymore info.

Thanks

Paul

 

[Amy Blankenship]

OK, try this:

Open your query in design view. Select the column with all the IIFs in it
and DELETE it.

Now, right click in the gray area and select "show table." Select your
table that shows what the post codes belong to. Click on the field in the
original table that has the short version of the post code. Now hold down
the mouse and drag that field to the corresponding field in the other table.
This creates a join relationship.

Now, double-click the other field in the lookup table. Run the query. Does
that look about like what you wanted?

HTH;

Amy

Hi,

I started with a table with about 8 columns in it and then pulled a query
together off that table with calculated fields in the query to do some
analysis of the source data.

Within the origional table there is a post code field. I striped the
postcode down to only show the first part of the postcode i.e. the alpha
part. What I need to do then is to put another calculated field into the
query to show where that post code belongs to, for e.g. WF = Wakefield, LS
=
Leeds etc. First I thought I could do this with "IIf([Post
Code]="WF","Wakefield","Unknown")" but then found that you could only have
13
else IIf statements which is no where near enough.

I have another table that shows where each postcode belongs to but I don't
know how to do a lookup like you use a VLOOKUP in excel.

There are 2 tables now in my query, not joined. One called Post Codes,
i.e.
the reference table and the origional source data table. The Post Code
table
contains 132 possible post code areas. I have put the following code into
the table:-

Test: IIf([Actual Post Code]=[UK Post Codes]![Post Code],[UK Post
Codes]![City/Town],"Unknown")

This works but the problem is it produces 132 rows per line of the
origional
source data table? How do I combat this so it only finds the code I want
and
produces just one results for each line of the origional source data??

Hope this is a bit more specific?

Thanks

Paul

To be more specific with you, you have to be more specific with us. So
what
is the structure of the table you are querying, or even the text of the
existing query? It may be that you can just join to the lookup table, as
Karl suggested.

HTH;

Amy

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what
you
are trying to do but am unsure what the calculated field code would be?
I
can obviously get the reference table into the query but how would I
then
say
"if that equals that then equal this? Can you please provide me with
some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

:

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

Hi,

I am currently running a query off a table in Access. I have a
calculated
field in the query which looks at the results of another column from
within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements,
then
Access
says the expression is too complicated. What else can I use so I
can
do
more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias
of
more
than 13?

I hope I have explained enough, please let me know if you need
anymore
info.

Thanks

Paul

 

[Guest]

Amy,

I can't do this because the short version of the postcode is already a
calculated field so therefore I can't create a join, for me to do what you
are saying I would have first take the select query with the calculated field
in it and make it into a make table query and then do another select query.

Anymore suggestions?

Is there nothing like a dlookup that would work?

Thanks

Paul

OK, try this:

Open your query in design view. Select the column with all the IIFs in it
and DELETE it.

Now, right click in the gray area and select "show table." Select your
table that shows what the post codes belong to. Click on the field in the
original table that has the short version of the post code. Now hold down
the mouse and drag that field to the corresponding field in the other table.
This creates a join relationship.

Now, double-click the other field in the lookup table. Run the query. Does
that look about like what you wanted?

HTH;

Amy

Hi,

I started with a table with about 8 columns in it and then pulled a query
together off that table with calculated fields in the query to do some
analysis of the source data.

Within the origional table there is a post code field. I striped the
postcode down to only show the first part of the postcode i.e. the alpha
part. What I need to do then is to put another calculated field into the
query to show where that post code belongs to, for e.g. WF = Wakefield, LS
=
Leeds etc. First I thought I could do this with "IIf([Post
Code]="WF","Wakefield","Unknown")" but then found that you could only have
13
else IIf statements which is no where near enough.

I have another table that shows where each postcode belongs to but I don't
know how to do a lookup like you use a VLOOKUP in excel.

There are 2 tables now in my query, not joined. One called Post Codes,
i.e.
the reference table and the origional source data table. The Post Code
table
contains 132 possible post code areas. I have put the following code into
the table:-

Test: IIf([Actual Post Code]=[UK Post Codes]![Post Code],[UK Post
Codes]![City/Town],"Unknown")

This works but the problem is it produces 132 rows per line of the
origional
source data table? How do I combat this so it only finds the code I want
and
produces just one results for each line of the origional source data??

Hope this is a bit more specific?

Thanks

Paul

To be more specific with you, you have to be more specific with us. So
what
is the structure of the table you are querying, or even the text of the
existing query? It may be that you can just join to the lookup table, as
Karl suggested.

HTH;

Amy

Hi,

Thanks for your response.

I have done lookups in excel before but not access. I understand what
you
are trying to do but am unsure what the calculated field code would be?
I
can obviously get the reference table into the query but how would I
then
say
"if that equals that then equal this? Can you please provide me with
some
sample code using x and y etc for field names?

Thanks very much for your help.

Paul

:

Have another table or column in an existing table that determines the
correct value and include something like this:

(Select DisplayValue FROM tblDisplayValues WHERE hiddenValue = [X])

HTH;

Amy

Hi,

I am currently running a query off a table in Access. I have a
calculated
field in the query which looks at the results of another column from
within
the table.

The calculated field basically says:-

IIf([X]="NW","London",IIF([X]="WF","Wakefield","Unknown"))

This works fine until you have more than 13 Else IIF statements,
then
Access
says the expression is too complicated. What else can I use so I
can
do
more
than 13 Else If statments??

I have a list of Postcodes that I need to look at to show where the
postcodes belong to? So I need to be able to use Multiple Else IIf
Statements.

Does someone have the code to be able to look at multiple criterias
of
more
than 13?

I hope I have explained enough, please let me know if you need
anymore
info.

Thanks

Paul

 

[Amy Blankenship]

Amy,

I can't do this because the short version of the postcode is already a
calculated field so therefore I can't create a join, for me to do what you
are saying I would have first take the select query with the calculated
field
in it and make it into a make table query and then do another select
query.

Try this...Delete the long version of the post code from your existing
query. Close and save. Now create a new query. When it prompts you to
select tables, select your lookup table and your query with the calculated
field. Create the join as before. Drag all the fields from the old query
down to the grid, plus the long version of the post code from the other
table.

For the record, it sounds like you have some serious database design issues.
I suggest you post your existing table structure and the business need
behind them to the tablesdbdesign forum.

HTH;

Amy