Automatically select value from a calculated field

[jimmy0305]

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) Fiel
using datediff function, and in the subform, I have a field name
(AgeNo)

There is a criteria for AgeNo Field depending on (Age) field for eac
patient

Criteria

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

MRN (Med. Rec. No) field & Age are in Main form and AgeNo field i
in a subform
I want the AgeNo field to automatically select ( 0, 1, 2 or 3 ) base
on the calculated unbound text field(Age) but for some reason, th
Dlookup function can't capture the Age(unboound field). Is ther
another way of doing this? Thanks..

here's the code
Private Sub Form_Current(
Dim bytAge As Byt

bytAge = Nz(DLookup("[Age]", "tblPatient_Demographics", "[MRN] = '
& Me.MRN & "'"), 0

Select Case bytAg
Case 18 To 4
Me.AgeNo =
Case 41 To 6
Me.AgeNo =
Case 61 To 7
Me.AgeNo =
Case 71 To 15
Me.AgeNo =
End Selec
End Su

Many thanks... :cry

 

[[MVP] S.Clark]

Dlookup finds a value in a table or query. So, if Age is an unbound control
on a form, then NO, dlookup is the wrong tool for the job.

Since this is in the on_current of the form, then you should be able to
directly refer to the control, as it lives on the same form.

bytAge = txtAge