IIF more 14 times, give error

[ping99]

Hi all,

I run this query that works fine but I add one more IIF
means more than 14 IIF gave an error : WHY

SELECT IIf([demicote2]="01.0",1,IIf([demicote2]="01.5",1.5,IIf([demicote2]="02.0",2,IIf([demicote2]="02.5",2.5,IIf([demicote2]="03.0",3,IIf([demicote2]="03.5",3.5,IIf([demicote2]="04.0",4,IIf([demicote2]="04.5",4.5,IIf([demicote2]="05.0",5,IIf([demicote2]="05.5",5.5,IIf([demicote2]="06.0",6,IIf([demicote2]="06.5",6.5,IIf([demicote2]="07.0",7,IIf([demicote2]="07.5",7.5)))))))))))))) AS demicote3
FROM coteMoyenne

What is another alternative to resolve if we need more
than 14 IIF inside the query, please help.

TIA

 

[DrSimon]

I assume that this happens because the stack gets full. But wouldn't the following do what you want more elegantly?

SELECT val(demicote2) AS demicote3
FROM coteMoyenne

Simon Rouse

 

[ThomasLafferty]

If you must have more than 14, I would suggest using VBA and a Select Case structure (for branching and control). For your present circumstance, it looks like you are needing to convert text to numeric value for the value of demicote2. Why not use Val([Demicote2]) and get rid of all of the IIFs?

Tom



Live once die twice; live twice die once.

 

[JPMontreal]

Hi,
Try Switch:

From Help file:

Switch(CityName = "London", "English", CityName = "Rome", "Italian", CityName = "Paris", "French")



Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[ping99]

Thanks everybody,

It works fine.

TTY later

 

[dhookom]

I don't think you should manage any function like this with an expression. There should be a small lookup table that contains the values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrSimon]

Interesting point Duane and normally I would agree with you. However in this case it looks like the requirement is to turn a string into a numeric value. This is achieved far more efficiently using Val() than by the use of a join to an extra table which would need to be maintained to contain every possible string value.
But we wait for ping99 to reply!
Simon

 

[dhookom]

DrSimon,
I would agree that if the business rule/specification allowed for the use of Val() for every condition, it would be much more efficient and certainly easier to maintain.

I guess I see more than 2 IIf()s and I am off creating a lookup table .

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IT4EVR]

The problem with the immediate if is that it iterates through each condition in the condition tree, unlike a regular if statement that will bail out when a condition is met. So for each row processed, you are iterating through 14 conditions. I believe the switch statement works in the same manner. Obviously this is not very efficient.

In essence you are building a do loop into each row that is processed. You may not see a difference on a small result set, but will see it in a big one.

So I would agree with Duane and build a look up table. You can still convert it to a string anyway.

 

[randy700]

So, it's your suggestion to build a lookup table to determine that:
"1.5" = 1.5
"2.0" = 2.0
"2.5" = 2.5

How far do we go? This could quickly get out of hand. When the requirement is simply to get a numeric value from a text field, the Val function is the most logical choice.

However, if I were the programmer, I'd take a look at the manner in which the data was originally entered. Possibly, it could have been entered into a numeric field to begin with, eliminating the need for any conversion.


Randy

 

[dhookom]

As I stated previously, I would use the Val() function if it was guaranteed to work forever. If it wasn't guaranteed, then I would create a lookup table that would "go" forever.

You may be right regarding the original data entry but
[li]we haven't been given a guarantee regarding the Val()[/li]
[li]we don't know anything about the application other than the text values[/li]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[IT4EVR]

Now that I look at how this function is working, no lookup table is necessary if it is running in the same progression as "7.5" = 7.5, etc.

DrSimons post looks correct, except you will get an error on a null.

try this:

SELECT Csng(Nz(demicote2)) AS demicote3
FROM coteMoyenne