Automatically select value from a calculated field
Hello to everyone...
I have a calculated(unbound) text field named(Age) on my main form. The Age is calculated based on patient's DOB(Date of Birth) Field using datediff function, and in the subform, I have a field named (AgeNo).
There is a criteria for AgeNo Field depending on (Age) field for each patient.
If Age is 18-40 = AgeNo should be 0
If Age is 41-60 = AgeNo should be 1
If Age is 61-70 = AgeNo should be 2
If Age is >71 = AgeNo should be 3
MRN (Med. Rec. No) field & Age are in Main form and AgeNo field is in a subform.
I want the AgeNo field to automatically select ( 0, 1, 2 or 3 ) based on the calculated unbound text field(Age) but for some reason, the Dlookup function can't capture the Age(unboound field). Is there another way of doing this? Thanks...
here's the code:
Private Sub Form_Current()
Dim bytAge As Byte
bytAge = Nz(DLookup("[Age]", "tblPatient_Demographics", "[MRN] = '" & Me.MRN & "'"), 0)
Select Case bytAge
Case 18 To 40
Me.AgeNo = 0
Case 41 To 60
Me.AgeNo = 1
Case 61 To 70
Me.AgeNo = 2
Case 71 To 150
Me.AgeNo = 3