Another DLookup Q.....

[Templar333]

Still working on my employee benefit query and thanks to you folks, I've come quite aways. The next thing I'm trying to do is this and I'm sure I got it all wrong....

Column in query requires an input from a lookup table [COMP] that has only 2 fields, [Age] and [Factor]. Taking another field from the query, [AGE AT 2002], I'm trying to get the right [Factor] amount into the column. Problem is [AGE AT 2002] has decimal points and so does [Age]. Example, in [COMP], the 2 fields have the following info:

[Age] [Factor]
65 1.0
64.83 0.9917
64.58 0.9732
.
.
.
55.17 0.5083
55.08 0.5042
55 0.50

And this is what I have down right now:

Result=DLookup("[COMP]![Factor]","[COMP]","[AGE AT 2002] >" & Age&)

Once again, many, many thanks in advance...Helm.

 

[Templar333]

btt...

Anyone? Thanks.

 

[LittleSmudge]

Your problem here templar333 is that DLookup will not take the NEXT largest.

If the WHERE clause in a DLookup identifies more than one record then it can only return the value of one so it "picks one" - Which one is not defined or guaranteed. ( It makes a fun Random number generator - but not usful for what you need. )

I think you'll need to do it via a recordset

rst.Open "SELECT Top 1 Factor FROM Comp WHERE [AGE AT 2002] > " & Age & " ORDER BY Factor Desc"

This returns only the Top1 record from the ordered subset and as Factor is Ordered in Decending order the Top1 will be the lowest value that matches .


QED ?


G LS

PS.
rst.Open is ADO speak.
If you're on Access 97 or earlier you'll need DAO recordset
db.OpenRecordset ("SELECT .. etc

 

[KyleS]

Templar,
Just to make sure, you're looking for the first value greater then the Age at 2002 right? Kyle

 

[Templar333]

Kyle, that is correct.

 

[KyleS]

littlesmudge is correct, DLookup won't give the "next" although I don't know how random it is, since it usually just goes down the order of the table as it stands (although take note of the word 'usually'). Either way, depending on what version of Access you're using (you can use ADO in '97 just download the MDAC version you need from Microsoft) you should create a recordset and get it that way, SQL will allow you to get the next value.

There is another option depending on your data...

The ages have decimals to provide for better acuracy, is there a standard incrementation to them?

eg 55.25, 55.30, 55.35, etc?

If so, this should work:

Result=DLookup(&quot;[COMP]![Factor]&quot;,&quot;[COMP]&quot;,&quot;[AGE AT 2002] > &quot; & Age & &quot; AND [AGE AT 22] < &quot; & (Age + Increment))

I wrote that on the fly so the syntax may need some re-tooling, but this is the right idea. Just replace Increment with what the increment is (IF there is one) and this should work for you. Otherwise, I'm thinking a Recordset is a good way to go. Kyle

 

[Templar333]

Kyle, the field [AGE AT 2002] does not have a standard increment. Neither does [Age]. FYI, I'm using Acess 2000.

LittleSmudge, I've never done a recordset before so I'm catching up on the reading as I reply.

Guys, I really appreciate both your advice and pointers.

Helm.

 

[rajeessh]

Little Smudge cant it be furthur simplified for Access
as If u need to pick of Maximum value???

Result=DMax(&quot;[COMP]![Factor]&quot;,&quot;[COMP]&quot;,&quot;[AGE AT 2002] >&quot; & Age&)

Pl correct me if i'm wrong

 

[KyleS]

rajeessh!
You're a genius! Change the DMax to DMin and you've got it!

Result=DMin(&quot;[COMP]![Factor]&quot;,&quot;[COMP]&quot;,&quot;[AGE AT 2002] >&quot; & Age)

This will give you the lowest value that's above the Age field, ie: the next highest value!
Kyle

 

[LittleSmudge]

Okay Templar333, rajeessh & Kyle between them have found a simple one liner that solves this problem - but keep up with your study of ADO recordsets because you'll find them extreemly valuable in the future.


Good luck.

G LS


( As for the DLookup selection of a &quot;random&quot; record - MS official line is that which record DLookup chooses to return the value from is &quot;UNDEFINED&quot; - which I translated for literary effect and to make it stick in the mind as 'random'. But you're right Kyle - it will appear predictable 95% of the time - it's the other 5% that causes the embarrassment! )


G LS

 

[Templar333]

Guys, what can I say. Thank you very, very much for all your help. I've tried:

Result=DMin(&quot;[COMP]![Factor]&quot;,&quot;[COMP]&quot;,&quot;[AGE AT 2002] >&quot; & Age)

and it works!

LittleSmudge, I've yet to take my eyes off the chapter on ADO. It's tad confusing right now but the understanding is building slowly.

Again, many thanks.

 

[Templar333]

Guys, what can I say. Thank you very, very much for all your help. I've tried:

Result=DMin(&quot;[COMP]![Factor]&quot;,&quot;[COMP]&quot;,&quot;[AGE AT 2002] >&quot; & Age)

and it works!

LittleSmudge, I've yet to take my eyes off the chapter on ADO. It's tad confusing right now but the understanding is building slowly.

Again, many thanks.

 

[rajeessh]

If the tip works out dont fail to cast a vote Sirs?