Limits of IIF?

A

anglicanista

I want a continuous form with a calculated field. But the calculation is
complex - it calculates someone's age. I have a Function CalcAge() (the
'long' age calculation, not using datediff) but I can't see the way to refer
to it in an IIF statement if the birthdate isn't null. Putting this in the
control source for the age test box:

=IIf(IsNull([Birthdate]),0,CalcAge())

correctly gives me 0 if there's no birthdate, but "#Error" if there is one.

So is it even possible to use a sub or function in an IIF?

newB
 
K

Ken Snell \(MVP\)

Don't you need to pass the Birthdate field to the function?

=IIf(IsNull([Birthdate]),0,CalcAge([Birthdate]))
 
J

John W. Vinson

I want a continuous form with a calculated field. But the calculation is
complex - it calculates someone's age. I have a Function CalcAge() (the
'long' age calculation, not using datediff) but I can't see the way to refer
to it in an IIF statement if the birthdate isn't null. Putting this in the
control source for the age test box:

=IIf(IsNull([Birthdate]),0,CalcAge())

correctly gives me 0 if there's no birthdate, but "#Error" if there is one.

So is it even possible to use a sub or function in an IIF?

newB

Sure, but you've got to pass it an argument! Doesn't CalcAge expect the
birthdate?

=IIf(IsNull([Birthdate]),0,CalcAge([Birthdate]))
 
A

anglicanista

Ken Snell (MVP) said:
Don't you need to pass the Birthdate field to the function?

=IIf(IsNull([Birthdate]),0,CalcAge([Birthdate]))

DOH! Works now!

thank you...

{slinks away with scarlet face}
 
K

Klatuu

Here is my version. It would avoid the use if the IIf statement.

Public Function CalcAge(Bdate As Variant, Optional DateToday As Variant) As
Integer
Dim dtmToDate As Date

' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
' Returns 0 if Bdate is Null
' Uses current date if DateToday is missing or Null

If IsNull(Bdate) Then
CalcAge = 0
Else
If IsMissing(DateToday) Then
dtmToDate = Date
Else
dtmToDate = Nz(DateToday, Date)
End If
If Month(dtmToDate) < Month(Bdate) Or (Month(dtmToDate) = _
Month(Bdate) And Day(dtmToDate) < Day(Bdate)) Then
CalcAge = Year(dtmToDate) - Year(Bdate) - 1
Else
CalcAge = Year(dtmToDate) - Year(Bdate)
End If
End If
End Function

--
Dave Hargis, Microsoft Access MVP


Ken Snell (MVP) said:
Don't you need to pass the Birthdate field to the function?

=IIf(IsNull([Birthdate]),0,CalcAge([Birthdate]))

--

Ken Snell
<MS ACCESS MVP>




anglicanista said:
I want a continuous form with a calculated field. But the calculation is
complex - it calculates someone's age. I have a Function CalcAge() (the
'long' age calculation, not using datediff) but I can't see the way to
refer
to it in an IIF statement if the birthdate isn't null. Putting this in the
control source for the age test box:

=IIf(IsNull([Birthdate]),0,CalcAge())

correctly gives me 0 if there's no birthdate, but "#Error" if there is
one.

So is it even possible to use a sub or function in an IIF?

newB
 

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