Help with expressions

  • Thread starter Thread starter Cesar Zapata
  • Start date Start date
C

Cesar Zapata

Hi,

I'm pretty new at access and I would appreciate if you help me with the
following.

I have this formula in excel
=IF(D4>1,D4,IF(AND(D4>=0.01,D4<=0.25),-2,IF(AND(D4>=0.26,D4<=0.75),0,IF(AND(
D4>=0.76,D4<=1),1,IF(D4=0,-5)))))

and I tried to make similar formula using the Event Expression. I have tried
everything without luck. I dont know what are my options.. either write it
in the expression or code as a case statements.

help me transforming this formula or pointing me to a place where I can get
some info untill my Book of Access arrives.

Thanks.
cz
 
Hi,

I'm pretty new at access and I would appreciate if you help me with the
following.

I have this formula in excel
=IF(D4>1,D4,IF(AND(D4>=0.01,D4<=0.25),-2,IF(AND(D4>=0.26,D4<=0.75),0,IF(AND(
D4>=0.76,D4<=1),1,IF(D4=0,-5)))))

and I tried to make similar formula using the Event Expression. I have tried
everything without luck. I dont know what are my options.. either write it
in the expression or code as a case statements.

help me transforming this formula or pointing me to a place where I can get
some info untill my Book of Access arrives.

Thanks.
cz
Click to expand...

The difficulty is that in Access terms like D4 have no meaning. Access
has Fields. You need to refer to a field when writing an expression.
Also Access, in this kind of expression, you would use an Immediate If
function (=IIf()).

=IIf([FieldA] >1,[FieldA],IIf([FieldA]>0.01 AND
[FieldA]<=0.25,-2,IIf([FieldA] >= etc.)))

Make sure you have the correct number of closing parenthesis at the
end (1 for each IIf).

The whole thing might be better written (and easier to follow) using a
Select Case statement in a module function and referring to the
function name in the control

See VBA help on "Using Select Case Statements" and "Select Case
statement".
 
The whole thing might be better written (and easier to follow) using a
Select Case statement in a module function and referring to the
function name in the control
Click to expand...

The OP could use the SWITCH function. The OP's formula has no
expression for values less than zero (maybe their scheme doesn't allow
this), so I've used minus 999 as a placeholder:

SELECT
MyFloatCol,
SWITCH(
MyFloatCol > 0.75, 1,
MyFloatCol<=0.75 AND MyFloatCol>0.25, 0,
MyFloatCol<=0.25 AND MyFloatCol>0, -2,
MyFloatCol=0, -5,
MyFloatCol<0, -999
) AS MyNewValue
FROM
MyTable
;

Jamie.

--
 
Back
Top