Nested If conversion to Select case

L

Lotto

I have a really long set of nested ifs that I need to do something
with. I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).

I thought a select case statement would work, or maybe there is a much
easier way to get what I need. I have no idea how to get this in a
function and working. Below is what I;m trying to do.Can anyone
help me?

Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
 
J

John W. Vinson

I have a really long set of nested ifs that I need to do something
with. I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).

I thought a select case statement would work, or maybe there is a much
easier way to get what I need. I have no idea how to get this in a
function and working. Below is what I;m trying to do.Can anyone
help me?

Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
Click to expand...


By far the simplest approach would be table driven. What is [A]? A form, a
table? Why do you have multiple (fields? controls?) with data stored in the
name?

If you do want a query expression, consider using the Switch() function. It
takes arguments in pairs; evaluates the pairs left to right; and when it first
finds a pair in which the first argument is TRUE it returns the second element
of the pair and quits. So:

Bracket1: Switch([A]![1] = ![P3], 1, [A].[10]=.[P3], 10, [A].[30] =
.[P3], 30, ..., [A].[1500] = .[P3], 1500, True, 0)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
L

Lotto

I have a really long set of nested ifs that I need to do something
with.  I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).
Click to expand...
I thought a select case statement would work, or maybe there is a much
easier way to get what I need.  I have no idea how to get this in a
function and working.    Below is what I;m trying to do.Can anyone
help me?
Click to expand...
Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
Click to expand...


By far the simplest approach would be table driven. What is [A]? A form, a
table? Why do you have multiple (fields? controls?) with data stored in the
name?

If you do want a query expression, consider using the Switch() function. It
takes arguments in pairs; evaluates the pairs left to right; and when it first
finds a pair in which the first argument is TRUE it returns the second element
of the pair and quits. So:

Bracket1: Switch([A]![1] = ![P3], 1, [A].[10]=.[P3], 10, [A].[30] =
.[P3], 30, ..., [A].[1500] = .[P3], 1500, True, 0)

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...


I tried a switch, the expression as a whole is too long (too many
characters, or too many nested ifs). That is why the field names are
one character, to make it as short as possible.

What I am trying to do is take a price in a table [P3] and compare it
across columns in another table [A] until it finds a hit. When it
does, it should retireve the column heading, which is a number.
It will work with up to 12 columns, when I added the last three, it
gave me the "too long" error.
 
B

Bob Barrows

Lotto said:
I have a really long set of nested ifs that I need to do something
with. I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).
Click to expand...
I thought a select case statement would work, or maybe there is a
much easier way to get what I need. I have no idea how to get this
in a function and working. Below is what I;m trying to do.Can anyone
help me?
Click to expand...
Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
Click to expand...
Click to expand...
Click to expand...

I tried a switch, the expression as a whole is too long (too many
characters, or too many nested ifs). That is why the field names are
one character, to make it as short as possible.

What I am trying to do is take a price in a table [P3] and compare it
across columns in another table [A] until it finds a hit. When it
does, it should retireve the column heading, which is a number.
It will work with up to 12 columns, when I added the last three, it
gave me the "too long" error.
Click to expand...

It sounds as if it's time to admit that your database designis not, as
John hinted, appropriate. You are storing data in metadata (object
names), which is not a good thing in the database world. Some people
refer to this practice as "committing spreadsheet". A good spreadsheet
design ("wide and short") can be an atrocious database design (for
databases, think "narrow and long"). Data should be stored as data, not
in field or table names. Just think how easy this task would be if you
had a price table like this:

Table: Prices
PriceType, Price
1 25
10 26
30 32
50 48
1500 258

Now, getting your answer is as simple as:

select [P3],PriceType FROM
join Prices As p ON .[P3]=p.Price

If the database design cannot be changed for some reason (given that you
have already taken the step of shortening your field and table names, I
would suspect your database design is not set in stone), then you have
to create a view to get those prices in the proper format to allow your
question to be answered. Do this by creating a saved union query called,
say, FoldedPrices, like this:

select 1 As PriceType,[1] FROM [A]
UNION ALL
select 10 As PriceType,[10] FROM [A]
UNION ALL
select 30 As PriceType,[30] FROM [A]
....
UNION ALL
select 1500 As PriceType,[1500] FROM [A]

select [P3],PriceType FROM
join FoldedPrices As p ON .[P3]=p.Price


Union queries are notoriously poor performers, so, if there is a lot of
data, you should be leaning toward changing your database design instead
of trying to work around the bad design.
 
L

Lotto

Lotto said:
I have a really long set of nested ifs that I need to do something
with. I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).
I thought a select case statement would work, or maybe there is a
much easier way to get what I need. I have no idea how to get this
in a function and working. Below is what I;m trying to do.Can anyone
help me?
Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
Click to expand...
Click to expand...


I tried a switch, the expression as a whole is too long (too many
characters, or too many nested ifs).  That is why the field names are
one character, to make it as short as possible.
Click to expand...
What I am trying to do is take a price in a table [P3] and compare it
across columns in another table [A] until it finds a hit.  When it
does, it should retireve the column heading, which is a number.
It will work with up to 12 columns, when I added the last three, it
gave me the "too long" error.
Click to expand...

It sounds as if it's time to admit that your database designis not, as
John hinted, appropriate. You are storing data in metadata (object
names), which is not a good thing in the database world. Some people
refer to this practice as "committing spreadsheet". A good spreadsheet
design ("wide and short") can be an atrocious database design (for
databases, think "narrow and long"). Data should be stored as data, not
in field or table names. Just think how easy this task would be if you
had a price table like this:

Table: Prices
PriceType, Price
1                25
10               26
30                32
50                48
1500            258

Now, getting your answer is as simple as:

select [P3],PriceType  FROM
join Prices As p ON .[P3]=p.Price

If the database design cannot be changed for some reason (given that you
have already taken the step of shortening your field and table names, I
would suspect your database design is not set in stone), then you have
to create a view to get those prices in the proper format to allow your
question to be answered. Do this by creating a saved union query called,
say, FoldedPrices, like this:

select 1 As PriceType,[1] FROM [A]
UNION ALL
select 10 As PriceType,[10] FROM [A]
UNION ALL
select 30 As PriceType,[30] FROM [A]
...
UNION ALL
select 1500 As PriceType,[1500] FROM [A]

select [P3],PriceType  FROM
join FoldedPrices As p ON .[P3]=p.Price

Union queries are notoriously poor performers, so, if there is a lot of
data, you should be leaning toward changing your database design instead
of trying to work around the bad design.

--
HTH,
Bob Barrows- Hide quoted text -

- Show quoted text -
Click to expand...


I understand what you are both trying to say, but I am just using the
DB to comapre two sets of data and reconcile some numbers. It is by
no means anything that will be used in a "normal" DB fashion. Don't
want to spend time on design, as it will be a throw away after use. I
used a DB as there were many different pieces that had to be joined
together and reformatted.

Thanks for your help, I'll try the union query.
 
T

tina

just to comment, because other developers - including newbies - may read
this thread over time: good tables/relationships structures are not just a
matter of "going by the rules". i can't speak to other software, due to lack
of other experience, but i can say that Access was designed to work
according to relational design rules. you can't very easily, or quickly, do
just about anything useful in Access, such as manipulate data, if you're not
working with a normalized structure. any time that you may think you're
saving by throwing together a non-normalized tables/relationships structure
is almost surely going to be lost - and then some - as you struggle to find
ways to work around that poor structure.

hth
tina


Lotto said:
I have a really long set of nested ifs that I need to do something
with. I tested with less statements, and it worked great, Now with
all the statements it is too long (gives me an error).
I thought a select case statement would work, or maybe there is a
much easier way to get what I need. I have no idea how to get this
in a function and working. Below is what I;m trying to do.Can anyone
help me?
Bracket1:
(IIf([A]![1]=![P3],1,
(IIf([A]![10]=![P3],10,
IIf([A]![30]=![P3],30,
IIf([A]![50]=![P3],50,
IIf([A]![200]=![P3],200,
(IIf([A]![300]=![P3],300,
IIf([A]![400]=![P3],400,
IIf([A]![500]=![P3],500,
IIf([A]![600]=![P3],600,
IIf([A]![650]=![P3],650,
IIf([A]![700]=![P3],700,
IIf([A]![1000]=![P3],1000,
(IIf([A]![1200]=![P3],1200,
IIf([A]![1500]=![P3],1500,
else 0)))))))))))))))
Click to expand...
Click to expand...


I tried a switch, the expression as a whole is too long (too many
characters, or too many nested ifs). That is why the field names are
one character, to make it as short as possible.
Click to expand...
What I am trying to do is take a price in a table [P3] and compare it
across columns in another table [A] until it finds a hit. When it
does, it should retireve the column heading, which is a number.
It will work with up to 12 columns, when I added the last three, it
gave me the "too long" error.
Click to expand...

It sounds as if it's time to admit that your database designis not, as
John hinted, appropriate. You are storing data in metadata (object
names), which is not a good thing in the database world. Some people
refer to this practice as "committing spreadsheet". A good spreadsheet
design ("wide and short") can be an atrocious database design (for
databases, think "narrow and long"). Data should be stored as data, not
in field or table names. Just think how easy this task would be if you
had a price table like this:

Table: Prices
PriceType, Price
1 25
10 26
30 32
50 48
1500 258

Now, getting your answer is as simple as:

select [P3],PriceType FROM
join Prices As p ON .[P3]=p.Price

If the database design cannot be changed for some reason (given that you
have already taken the step of shortening your field and table names, I
would suspect your database design is not set in stone), then you have
to create a view to get those prices in the proper format to allow your
question to be answered. Do this by creating a saved union query called,
say, FoldedPrices, like this:

select 1 As PriceType,[1] FROM [A]
UNION ALL
select 10 As PriceType,[10] FROM [A]
UNION ALL
select 30 As PriceType,[30] FROM [A]
...
UNION ALL
select 1500 As PriceType,[1500] FROM [A]

select [P3],PriceType FROM
join FoldedPrices As p ON .[P3]=p.Price

Union queries are notoriously poor performers, so, if there is a lot of
data, you should be leaning toward changing your database design instead
of trying to work around the bad design.

--
HTH,
Bob Barrows- Hide quoted text -

- Show quoted text -
Click to expand...


I understand what you are both trying to say, but I am just using the
DB to comapre two sets of data and reconcile some numbers. It is by
no means anything that will be used in a "normal" DB fashion. Don't
want to spend time on design, as it will be a throw away after use. I
used a DB as there were many different pieces that had to be joined
together and reformatted.

Thanks for your help, I'll try the union query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested IIf Statements 5
crosstab problem 2
Nested IIf: Define ranges, >= And <=? 2
First value below zero in a column 1
Optimization question 2
Optimization project 2
Alternatives to Nested If statements? 10
Multiple calculations on same field in Query, by range of values 5

Top