Query expression to simulate vlookup/true

[Sephaerius]

I'm trying to convert an Excel template over to Access, and in doing so I need to simulate a vlookup/true formula within my query.
In the column I'm trying to return, I am using the DateDiff() expression to capture the number of days between two dates, which will range from 0 into the thousands. I have joining table that lumps together aging "buckets", which in Excel is done via vlookup/true (closest match). It looks like this:


AGE ....AGING BUCKET
0 .........0-30
31 .......31-60
61 .......61-90
91 .......91-120
121 .....121-150

And so on, etc.

Is there a query expression or joining method that will achieve the same result?

Thanks,

[aytee111]

First you will need to create a field in a query that returns the AGING BUCKET and you can use that to join on in a second query. You can do it either with one long IIf statement in the query or use a VBA function (functions can make queries run slower so if you have very many records this should be avoided).

[andy49]

Two approaches here

Either create a table in excel with AgeValue and agegroup then import it into access. Then join this to your table which has the age?


Or use a function. If created inside a module it can be called from any query

Function Agegroup (agevalue as integer) as string
Select case agevalue
Case<31
Agegroup = "0-30"
Case 31 to 60
agegroup   = "31-60"
case 61 to 90
agegroup = "61-90"
case 91-120
agegroup = "91-120"
case >121
agegroup = "121-150" 
End case
End function