IIF statement or Switch()

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a this syntax located in the control source of a testbox on my form

=IIf(qryCalculations!RecPower<"3",3,IIf(qryCalculations!RecPower<"5",5,IIf(qryCalculations!RecPower<"7.5",7.5,IIf(qryCalculations!RecPower<"9.99",10,IIf(qryCalculations!RecPower<"15","15",IIf(qryCalculations!RecPower<"20.0","20","Error!"))))))

This only works upto returning the value of "10", how would i use the switch
function and where would I put it?

Can I leave the function in the control source?
 
Hi Belle,

Mu guess that "Switch" statement also have limits like in "IIF".
I do not know if "Select Case" has the limits or not. But you can try that.
Lookup "Select Case" in help.

Depends on when do want it to happen then you can decide where you can put
the code.

For example, if you want to happen when you open the form, then you have to
put the code in Form Open event. If you want to happen when you change the
value, then you have to put the code in the After Update event of that object
(textbox, combobox, etc).

Hope this helps.
 
Belle said:
I have a this syntax located in the control source of a testbox on my form

=IIf(qryCalculations!RecPower<"3",3,IIf(qryCalculations!RecPower<"5",5,IIf(qryCalculations!RecPower<"7.5",7.5,IIf(qryCalculations!RecPower<"9.99",10,IIf(qryCalculations!RecPower<"15","15",IIf(qryCalculations!RecPower<"20.0","20","Error!"))))))

This only works upto returning the value of "10",
Click to expand...

Are you sure it's "working?" Because you have enclosed the RecPower
values in quotes, I am assuming that they are actually text, not
numbers. If this is the case, then this expression is not "working" the
way I think you want it to. If RecPower is in fact a text field, the
numeric characters will be evaluated as *text* even though they look
like numbers. For example, if RecPower contains the numeric character
3, this expression returns a 5. If RecPower = "5," this returns "7.5."
Is this really what you want?

how would i use the switch
function and where would I put it?

Can I leave the function in the control source?
Click to expand...

Yes, but the SWITCH function won't make any real difference, other than
to make the expression a litle trimmer by cutting down on the nested
IIFs. SWITCH() evaluates multiple expressions in sequence (left to
right) and returns a particular value if a particular expression
evaluates as True. An example would be

SWITCH((qryCalculations!RecPower<"3",3,qryCalculations!RecPower<"5",5,qryCalculations!RecPower<"7.5",7.5,
qryCalculations!RecPower<"9.99",10,qryCalculations!RecPower<"15","15",qryCalculations!RecPower<"20.0",20,
qryCalculations!RecPower>"20.0","Error!")

But this will still have the same logical problems as your original
expression. It looks to me that you are trying to convert text to
values. Assuming you have some reason for keeping the RecPower values
as text & want to convert them to actual numbers in order to do some
kind of math with them in the form, I'd suggest using Val() or Round().
Or have I misunderstood you completely? If so, please clarify.
 
LeAnne said:
Are you sure it's "working?" Because you have enclosed the RecPower
values in quotes, I am assuming that they are actually text, not
numbers. If this is the case, then this expression is not "working" the
way I think you want it to. If RecPower is in fact a text field, the
numeric characters will be evaluated as *text* even though they look
like numbers. For example, if RecPower contains the numeric character
3, this expression returns a 5. If RecPower = "5," this returns "7.5."
Is this really what you want?

how would i use the switch

Yes, but the SWITCH function won't make any real difference, other than
to make the expression a litle trimmer by cutting down on the nested
IIFs. SWITCH() evaluates multiple expressions in sequence (left to
right) and returns a particular value if a particular expression
evaluates as True. An example would be

SWITCH((qryCalculations!RecPower<"3",3,qryCalculations!RecPower<"5",5,qryCalculations!RecPower<"7.5",7.5,
qryCalculations!RecPower<"9.99",10,qryCalculations!RecPower<"15","15",qryCalculations!RecPower<"20.0",20,
qryCalculations!RecPower>"20.0","Error!")

But this will still have the same logical problems as your original
expression. It looks to me that you are trying to convert text to
values. Assuming you have some reason for keeping the RecPower values
as text & want to convert them to actual numbers in order to do some
kind of math with them in the form, I'd suggest using Val() or Round().
Or have I misunderstood you completely? If so, please clarify.
Click to expand...
 
LeAnne,

I understand the problem now, you're right!! The current expression is
reading it as text. For example, when RecPower "45" , my result is '5' How
can I correct this problem. I see you mentioned the Val() method. But how
can I implement it?

Thnak you
 
LeAnne,

You're absolutely right, How can I construct my statement so that it will
read as a number and not text. I have not used the VAL() before
 
I'd start by looking at qryCalculations. Is RecPower being calculated in
this query? What are the underlying tables from which qryCalculations is
drawing data to make this calculation? Is/are the field(s) in the
underlying table(s) of numeric datatype? And finally, what exactly is it
that you want to *do* with the RecPower values?

The easiest thing to do would be to change the datatype of the field(s)
to Number (Double) in the underlying table(s). This is what I'd
recommend if you want Access to always treat the RecPower values as
numbers. Now, if you have some reason for wanting to keep them as text,
and you want to display the RecPower values for a given record on the
form, then the datatype doesn't absolutely have to be converted. To use
the value displayed in the RecPower textbox to perform another
calculation on the form, just substitute Val([RecPower]) for [RecPower]
in whatever expressions are referencing that bit of information.

hth,

LeAnne
 
Yes, RecPower is being calculated in qryCalculations. In the underlying
table all datatypes are Number ( some are Single and Long Integer)
Ultimatley, I want the resulting Power to be viewed in a report according to
which classification RecPower falls into.

LeAnne said:
I'd start by looking at qryCalculations. Is RecPower being calculated in
this query? What are the underlying tables from which qryCalculations is
drawing data to make this calculation? Is/are the field(s) in the
underlying table(s) of numeric datatype? And finally, what exactly is it
that you want to *do* with the RecPower values?

The easiest thing to do would be to change the datatype of the field(s)
to Number (Double) in the underlying table(s). This is what I'd
recommend if you want Access to always treat the RecPower values as
numbers. Now, if you have some reason for wanting to keep them as text,
and you want to display the RecPower values for a given record on the
form, then the datatype doesn't absolutely have to be converted. To use
the value displayed in the RecPower textbox to perform another
calculation on the form, just substitute Val([RecPower]) for [RecPower]
in whatever expressions are referencing that bit of information.

hth,

LeAnne
LeAnne,

You're absolutely right, How can I construct my statement so that it will
read as a number and not text. I have not used the VAL() before

:
Click to expand...
Click to expand...
 
LeAnne,

I finally got my function to work correctly, thank you so much for your help.
By the way, can you help me with querying. If so, please read "Querying
column function", it's 6 entries beneath this one.
 
Hi,


Have a table, like

Ranges 'table name
FromThis, ToThis, UseThis ' fields
0, 3, 3
3, 5,, 5
5, 7.5, 7.5
7.5, 9.99, 10
10, 15, 15
15, 20, 20
20, 1E300, Error


and something like:

SELECT a.*, Ranges.UseThis
FROM tableA, Ranges
WHERE a.RecPower>= Ranges.FromThis AND a.RedPower < Ranges.ToThis



Your data is then where it should be, in a table, not in the code, and thus,
easier to be managed and modified by your end user, without having your
client to change YOUR CODE. You can also use DLookup rather than the
proposed query.



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thank you, what is 'a' does is stand for 'array'. Soory for being so
oblivious

But please explain further

Belle
 
Hi,


Late-editing. I should have type

SELECT TableA.*, Ranges.UseThis
FROM TableA, Ranges
WHERE TableA.RecPower>= Ranges.FromThis AND TableA.RecPower < Ranges.ToThis



and TableA is probably to be replaced with qryCalculations, from your
initial post.




Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thanks for replying and for the help. I have another question.
I am trying to calculate a field within a query. But the column, where the
information is located is not being returned, even if I change the bound
column.
I've been working on this problem for the past 3 days but no success. Do
you have any ideas.
I have also typed this question 6 email entries beneath this one. its called
"Querying column function"

Thanks for any suggestions
 
Hi Belle,

Just a wild guess...did you by any chance use Access' "Lookup Wizard"
(mis)feature when you created the field (column) where the information
you need is located?

LeAnne
 
Back
Top