AGE RANGE SELECTION

  • Thread starter Thread starter Jasmine
  • Start date Start date
J

Jasmine

I need some help with figuring this problem.
I have a table called - RETENSION with the following Fields-

ID ( Autonumber)
ISSUE AGE ( Number)
ISSUE AMT ( Number)

I have a Table Called - CLIENT with the following Fields

ID ( Autonumber)
CLIENTNAME (Text)
DOB (Date/Time)
AGE (Number)

This is a sample data in the Retension Table:

ISSUE AGE ISSUE AMT
0-24 $5,000,000
25-70 10,000,000
71-75 5,000,000
76-80 2,500,000
81-85 1,250,000

I have a Client Form for the client table .
Using Dlookup I want to be able to lookup the Client Age within the Age
constraint
and return the Issue Amount

Don't know if Dlookup is the most smartest way to do this.
Was thinking of some query - but don't know the syntax

I hope I'm not asking for the imposible or if there's a more simpler way.


Thanks.
 
Jasmine - it looks like [ISSUE AGE] is not actually a number, so you'll have
to determine the start and end ages of the range so you can see if the
[Client Age] fits between. Depending on your level of programming, you
might be better off adding two columns to your Retension table, ie StartAge
and EndAge. Alternatively, if you're comfortable enough with writing code,
you could determine the position in the string of the "-" character and put
the start and end ages into variable arrays, then run through the arrays
until you find the appropriate range.
 
Jasmine,

I strongly discourage storing the [Age] in a field. Since this value
changes quite frequently (for at least some portion of your client base), it
is much smarter to compute this value on the fly.

There are several techniques for this, but I liked the one that James
Fortune posted a couple of days ago

CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

HTH
Dale
 
Back
Top