Birthdate and Null Equals Error 1

[KayJen]

Hi,
I have a field labeled BirthDate on a Form labeled frm_Clients. Next to the Birthdate field is an unbound boxed labeled Age with the following in the Control Source: =Age([BirthDate]). Now to my problem. I also have and unbound box titled: Status, with the following in the Control Source: =IIf([Age]<17,&quot;Juvenile Member&quot;,&quot;Adult Member&quot, this all works fine, until a Date of Birth is unknown. I need help in modifying this so that is the BirthDate field is empty it will say something like, Birth Data Missing. Any help will be more than greatly appreciated.

P.S. While on the topic, being that Age is an unbound box, is there a way to enter the actual age while leaving the BirthDate field blank.

Many Thanks,
Sonny

 

[markgrizzle]

To display actual age in the box, use the formula year(Date()) - year([BirthDate]).

To handle multiple tests in an immediate if statement, place the second test in the else position. This changes it into an ElseIf

Iif(IsNull([Age]), &quot;Unknown&quot;, Iif([Age]<17, &quot;Juvenile&quot;, &quot;Adult&quot)

Make sure you don't chain too many tests in one statement, as performance will suffer.

 

[redapples]

Try putting an allocation of the recordsource into code
use something like

If Not IsNull(Birthdate) Then
'enter code here to allocate the recordsouce
'e.g. memberstatus.recordsource = IIf([Age]<17,&quot;Juvenile Member&quot;,&quot;Adult Member&quot;)
Else
'some code to indicate an error
'e.g. memberstatus.text = &quot;Unavailable&quot;
End If

This will obviously need modification but should get you to where you want to go.

 

[KayJen]

Mark,

Tried, your suggestion, still receiving #Error.

Thanks, Sonny

 

[markgrizzle]

Hi Sonny:
I'm assuming you mean the iif statement. I reread your original post, and realize you're using an Age() function. Check the function to see what it returns when Birthdate is unknown. If it's using a select case to test for values, you probably need a case else to return zero.

Then modify the iif statement as follows:

Iif([Age]=0, &quot;Unknown&quot;, Iif([Age]<17, &quot;Juvenile&quot;, &quot;Adult&quot)

Mark

 

[KayJen]

Mark,

Sorry for the late reply, but, we are having our office roof re-done, so we've lost power. I am using my laptop and just tried your suggestion and it works fine, thank you.


Thanks,
Sonny