If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
AGE RANGE SELECTION
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. |
#3
|
|||
|
|||
AGE RANGE SELECTION
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. |
#4
|
|||
|
|||
AGE RANGE SELECTION
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. |
Thread Tools | |
Display Modes | |
|
|