Problem with IIf

G

Guest

Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between "150001" And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of doing
it??

Thank you in advance

Paula
 
R

Randy Harris

PaulaCMT said:
Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between "150001" And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of doing
it??

Thank you in advance

Paula

Paula,

I think you're getting an ASCII comparison, rather than numeric, because you
have the values in quotes. That's the reason that 90000 returns "£780" and
probably 300 would return "£360".

HTH,
Randy
 
T

tina

the problem is that the Propertyvalue field is a Text data type, rather than
a Number data type. even though you type in numbers, Access sees them as
Text and compares them accordingly. suggest you change the field's data type
(in the table design view) to Number, field size Long Integer. and you'll
probably want to remove the Default Value of zero (0) that Access
automatically adds to Number fields.

also, rather than multiple nested IIf() functions, you might use the
Switch() function, as

=Switch([NumField]<1 Or [NumField]>1000000 Or [NumField] Is
Null,"0",[NumField]<100001,"240",[NumField]<150001,"280",[NumField]<200001,"
330",[NumField]<250001,"360",[NumField]<300001,"380",[NumField]<400001,"470"
,[NumField]<500001,"520",[NumField]<600001,"580",[NumField]<700001,"650",[Nu
mField]<800001,"725",[NumField]<900001,"780",[NumField]<1000001,"895")

replace [NumField] with your [Propertyvalue] field, of course. and add in
the "pound" sign, too, where appropriate - or simply format the calculated
control to show pounds (Currency, perhaps?).

hth
 
G

Guest

Thank you Thank you Thank you - you are a star!! This has been driving me mad!!

Paula

Randy Harris said:
PaulaCMT said:
Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between "150001" And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of doing
it??

Thank you in advance

Paula

Paula,

I think you're getting an ASCII comparison, rather than numeric, because you
have the values in quotes. That's the reason that 90000 returns "£780" and
probably 300 would return "£360".

HTH,
Randy
 
G

Guest

Thanks Tina. The data type was already set to Number as I have another
calculation already using that field so I did check that! I have got it
working fine now. Thank you for your suggestion. I can see that using the
Switch function would entail less typing!! Do Switch functions perform better
than IIf functions? Which one do you choose and why and where etc etc

Paula

tina said:
the problem is that the Propertyvalue field is a Text data type, rather than
a Number data type. even though you type in numbers, Access sees them as
Text and compares them accordingly. suggest you change the field's data type
(in the table design view) to Number, field size Long Integer. and you'll
probably want to remove the Default Value of zero (0) that Access
automatically adds to Number fields.

also, rather than multiple nested IIf() functions, you might use the
Switch() function, as

=Switch([NumField]<1 Or [NumField]>1000000 Or [NumField] Is
Null,"0",[NumField]<100001,"240",[NumField]<150001,"280",[NumField]<200001,"
330",[NumField]<250001,"360",[NumField]<300001,"380",[NumField]<400001,"470"
,[NumField]<500001,"520",[NumField]<600001,"580",[NumField]<700001,"650",[Nu
mField]<800001,"725",[NumField]<900001,"780",[NumField]<1000001,"895")

replace [NumField] with your [Propertyvalue] field, of course. and add in
the "pound" sign, too, where appropriate - or simply format the calculated
control to show pounds (Currency, perhaps?).

hth


PaulaCMT said:
Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between "150001" And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of doing
it??

Thank you in advance

Paula
 
R

RobFMS

Have you considered making a public function and calling the public function
from either code or from a query?
I suggest this because its easier to handle for code maintainence, then a
long-winded IIF() statement.
I only did a few of the case statements but I think its obvious to see where
you can fill in the rest.

If you have to break up the range between 1 and 100000, its MUCH easier to
handle the case statement.
Because this is a public function, you can call it from code or you can call
it from a query (if needed).

HTH

Rob Mastrostefano


Public Function GetPoundValue( _
byVal plngValue as long) As String

Dim strReturnValue As String

Select Case plngValue

Case 1 To 100000
strReturnValue = "£240"

Case 100001 To 150000
strReturnValue = "£280"

Case Else
strReturnValue = "???"

End Select

GetPoundValue = strReturnValue

End Function


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


Randy Harris said:
PaulaCMT said:
Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between "150001" And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of
doing
it??

Thank you in advance

Paula

Paula,

I think you're getting an ASCII comparison, rather than numeric, because
you
have the values in quotes. That's the reason that 90000 returns "£780"
and
probably 300 would return "£360".

HTH,
Randy
 
J

John Vinson

Can anyone please help me with this or maybe suggest a better way of doing
it??

Another - possibly better - way would be to use a "range table". Using
either IIF() or Switch() has the disadvantage that the business logic
(the value associated with a given range of property values) is buried
in the code, where it's hard to maintain, rather than in a Table.

You might want to consider having a small table Range with three
fields - Low, High, and Result, with values like

0; 100000; 240
100000; 150000; 280
150000; 200000; 330

and so on.

Join this table into your Query with a JOIN clause (entered in the SQL
window, you can't do it in the grid)

ON [yourtable].[Propertyvalue] > [Range].[Low] AND
[yourtable].[Propertyvalue] <= [range].[High]

This will let you pick up the Result in a query, without any IIF's,
AND's or BUT's.

John W. Vinson[MVP]
 
D

Duane Hookom

BINGO!

--
Duane Hookom
MS Access MVP


John Vinson said:
Can anyone please help me with this or maybe suggest a better way of doing
it??

Another - possibly better - way would be to use a "range table". Using
either IIF() or Switch() has the disadvantage that the business logic
(the value associated with a given range of property values) is buried
in the code, where it's hard to maintain, rather than in a Table.

You might want to consider having a small table Range with three
fields - Low, High, and Result, with values like

0; 100000; 240
100000; 150000; 280
150000; 200000; 330

and so on.

Join this table into your Query with a JOIN clause (entered in the SQL
window, you can't do it in the grid)

ON [yourtable].[Propertyvalue] > [Range].[Low] AND
[yourtable].[Propertyvalue] <= [range].[High]

This will let you pick up the Result in a query, without any IIF's,
AND's or BUT's.

John W. Vinson[MVP]
 
T

tina

I can see that using the
Switch function would entail less typing!! Do Switch functions perform better
than IIf functions? Which one do you choose and why and where etc etc

well, i don't know about "better". which i use depends on the circumstances.
as you noticed, the Switch() function does entail less typing, and perhaps
the logic is a little more easily understood at a glance - multiple nested
IIf() functions can get pretty convoluted. one drawback of the Switch()
function is that there's no "Else" argument - you need to cover all
contingencies explicitly in the argument, or you're liable to get unexpected
results. btw, a "cousin" of IIf() and Switch() is the Choose() function,
which can also be useful in handling returns based on multiple parameters.
you can read up on each of the functions in Access Help, to better
understand how they work.

personally, i can't really remember the last time i used a Switch() function
in a database - i'd be much more inclined to write a custom VBA function,
probably using the Select Case statement (see the post by RobFMS, elsewhere
in this thread). but if you're not comfortable using VBA, the built-in
functions are there.

also, recommend you take a look at MVP John Vinson's table solution
elsewhere in this thread. it gives you the same return values, but as he
points out, it has the advantage of exposing the parameter values to easy
updates. generally speaking, it's a good idea to NOT "hard-code" data values
into VBA when you can avoid it, because it's such a pain to maintain the
code. on top of that, having to update hard-coded values in code, especially
in multiple locations, is much more error-prone and harder to troubleshoot -
as opposed to having all the values in one place, easy to access and easy to
verify.

hth


PaulaCMT said:
Thanks Tina. The data type was already set to Number as I have another
calculation already using that field so I did check that! I have got it
working fine now. Thank you for your suggestion. I can see that using the
Switch function would entail less typing!! Do Switch functions perform better
than IIf functions? Which one do you choose and why and where etc etc

Paula

tina said:
the problem is that the Propertyvalue field is a Text data type, rather than
a Number data type. even though you type in numbers, Access sees them as
Text and compares them accordingly. suggest you change the field's data type
(in the table design view) to Number, field size Long Integer. and you'll
probably want to remove the Default Value of zero (0) that Access
automatically adds to Number fields.

also, rather than multiple nested IIf() functions, you might use the
Switch() function, as

=Switch([NumField]<1 Or [NumField]>1000000 Or [NumField] Is
Null,"0",[NumField]<100001,"240",[NumField]<150001,"280",[NumField]<200001,"
330",[NumField]<250001,"360",[NumField]<300001,"380",[NumField]<400001,"470"
,[NumField]<500001,"520",[NumField]<600001,"580",[NumField]<700001,"650",[Nu
mField]<800001,"725",[NumField]<900001,"780",[NumField]<1000001,"895")

replace [NumField] with your [Propertyvalue] field, of course. and add in
the "pound" sign, too, where appropriate - or simply format the calculated
control to show pounds (Currency, perhaps?).

hth


PaulaCMT said:
Hi there

Here is my problem.....

=IIf([Propertyvalue] Between "1" And "100000","£240",IIf([Propertyvalue]
Between "100001" And "150000","£280",IIf([Propertyvalue] Between
"150001"
And
"200000","£330",IIf([Propertyvalue] Between "200001" And
"250000","£360",IIf([Propertyvalue] Between "250001" And
"300000","£380",IIf([Propertyvalue] Between "300001" And
"400000","£470",IIf([Propertyvalue] Between "400001" And
"500000","£520",IIf([Propertyvalue] Between "500001" And
"600000","£580",IIf([Propertyvalue] Between "600001" And
"700000","£650",IIf([Propertyvalue] Between "700001" And
"800000","£725",IIf([Propertyvalue] Between "800001" And
"900000","£780",IIf([Propertyvalue] Between "900001" And
"1000000","£895","0"))))))))))))


This works fine except for values less than 100,000. For example 90,000
returns a fee of £780 instead of £240.

Can anyone please help me with this or maybe suggest a better way of doing
it??

Thank you in advance

Paula
 
G

Guest

Thank you all for your very helpful posts. I appreciate the time taken to
answer my query.

Paula
 
J

John Spencer

Tina,

Just a minor aside.

Switch does have an ELSE capability (kind of), just add one last condition with
True as its value.

Switch(x=1,1,x=2,4,Y=22,8,True,0)

If x = 3 and y = 9 then the above will return 0
 
T

tina

hey, that's handy, John - i like it! :)


John Spencer said:
Tina,

Just a minor aside.

Switch does have an ELSE capability (kind of), just add one last condition with
True as its value.

Switch(x=1,1,x=2,4,Y=22,8,True,0)

If x = 3 and y = 9 then the above will return 0
 

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

Top