Mulitple / Nested IIF in Access

[generaluser]

I want to be able to do a nested "If" statement like the ones in C and C++ in Access. Is it possible? If not, is it possible in Excel? What about if your result depends on two different criteria. (For example, If ([field 1]= 4 AND [field 2]>5) THEN [field3] = 45.)

Please anyone, let me know.
cmonique_hill@hotmail.com

 

[jebry]

Hi!

VBA handles nested ifs and the Select Case structures. SQL uses the IIf statement, but I think that is limited to eight levels. In Excel you can still use VBA, but you can sometimes have trouble finding the correct event to run it in. You can always hide a couple of columns and do 'intermediate calculations there and use the results to build a more complex IIf statement than the eight levels allowed.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[oharab]

If you are doing it in VBA (ie in a module) then simply nest them:

if x=1 then
if y=2 then
'do something
end if
end if

You can use and/or:

if x=1 and y=2 then
'do something
end if

if you are doing it in an expression,ie in a query or calculated field then use the iff function

=iif(x=1,iif(y=2,1,0),0)

or

=iif(and(x=1,y=2),1,0)

in excel the format is the same as the iif function, but you use if instead of iif

HTH

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[generaluser]

Now I have another question,

say either [field1] or [field2] has no data. How do I make it so that if [field1] or [field2] is null [field 3] is null. All of this within the above expression.

 

[jebry]

Hi!

Just use the logic you describe:

IIf(IsNull([field1])=True Or IsNull([field2])=True,Null,etc.)

If you are using VBA:

If IsNull([field1]) = True Or IsNull([field2]) = True Then
[field3] = Null
Else
etc.

hth
Jeff Bridgham
bridgham@purdue.edu