AGE RANGE SELECTION

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" wrote:

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.

For details on how to do this with a query rather than DLookup(), see Tom
Ellision's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jasmine" wrote in message
...
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,

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
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Jasmine" wrote:

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.