Re: Else If Statements

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

"Amy Blankenship" wrote:

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

"Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A5B4B14A-8064-42F6-82D8-18DD0C7C1062@xxxxxxxxxxxxxxxx
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

"Amy Blankenship" wrote:

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

"Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:81930A4C-93BB-4217-B14E-F3CC2EF86EF7@xxxxxxxxxxxxxxxx
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

"Amy Blankenship" wrote:

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

"Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5A9F28CC-0912-44A0-B8D7-5AA0ABCC48E4@xxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: Else If Statements
    ... Open your query in design view. ... Within the origional table there is a post code field. ... postcode down to only show the first part of the postcode i.e. the alpha ... This works fine until you have more than 13 Else IIF statements, ...
    (microsoft.public.access.queries)
  • Re: Else If Statements
    ... I started with a table with about 8 columns in it and then pulled a query ... Within the origional table there is a post code field. ... postcode down to only show the first part of the postcode i.e. the alpha ... What I need to do then is to put another calculated field into the ...
    (microsoft.public.access.queries)
  • Re: Else If Statements
    ... I can't do this because the short version of the postcode is already a ... are saying I would have first take the select query with the calculated ... Try this...Delete the long version of the post code from your existing ... behind them to the tablesdbdesign forum. ...
    (microsoft.public.access.queries)
  • RE: Limit Text displayed in Combo box
    ... I would base the combo box selection off a query rather than a table. ... you can use a calculated field in a query to return all ... I have a form with a combobox that uses the table as its row source. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Passing variables to Function
    ... Ok here is the calculated field from my query and my primary function. ... add previous cycle totals if they exist. ... be called from the qryReagent1 query. ...
    (microsoft.public.access.modulesdaovba)
Loading