Text Box (1 Viewer)

[lorraine]

Hopefully someone can help me with this small problem
I have an unbound text box on a form.
I want it to show either 'Junior', 'Adult' or 'Retired' depending on the dob of the client. I've tried different solutions but nothing seems to work properly. Any help woulr be appreciated

Thanx in advance
Lorraine

 

[R. Hicks]

Maybe this will help you:
Copy the following to a module:

Public Function GetStatus(dtDOB As Variant) As String
If IsNull(dtDOB) Or Not IsDate(dtDOB) Then Exit Function
Dim intAge As Integer
intAge = DateDiff("yyyy", dtDOB, Date) + _
(Date < DateSerial(Year(Date), Month(dtDOB), Day(dtDOB)))
Select Case intAge
Case Is >= 65
GetStatus = "Retired"
Case Is >= 21
GetStatus = "Adult"
Case Else
GetStatus = "Junior"
End Select
End Function

To use the function in an unbound txtbox, place the following as the control source of the txtbox:

=GetStatus([DOB])

Change "DOB" to the actual name of the DOB field from your form.

Alter the ages in the function to set the break points you want for each category.

HTH
RDH


[This message has been edited by R. Hicks (edited 11-13-2000).]

 

[lorraine]

Thanks R.Hicks

It worked perfectly!!!

Lorraine

 

[lorraine]

That code worked great but I have another problem now.
I hope I can explain it correctly.

On another form there is a DateJoined field. I need to display the status ie junior, adult, retired at the date joined. However this wont chane as they get older.

I hope I've explained mysellf clearly. Any ideas?
Thanx in advance

Lorraine

 

[R. Hicks]

If I understand you correctly, the same function should work with this also. You need only to change the function's argument:

=GetStatus([DateJoined])

This should be added to the control source property of the txtbox in the form where you wish to see this info.

HTH
RDH

 

[lorraine]

Thanks again for the reply

However I didnt make myself clear.

I have a Dob field, an Age field and a datejoined field. The age field calculates fine with the code you gave me. however if I use the same code for the datejoined filed the result is always "junior" as the datejoined is always recent. I need to obtain the status when joined from the dob but it will always remain the same once entered.

Thanks again in advance
Lorraine