A Look Up Function for Microsoft® Access

How to create a VBA procedure in Microsoft Access that behaves like Excel's VLOOKUP function but with additional functionality.

Last updated on 2024-05-18.

Introduction

Consider needing to look up a value in a list that matches a certain criterion. For example, to look up a person's weight on a certain date recorded in this table:

Access's Dlookup function will return a value from the table, as in this example for March 1st 2019 in which it would return 79.3:

Dlookup("Weight","tblBodyWeight","DateWeight=#03/01/2019#")

(The use of the # character signifies to Access that the 03/01/2019 is a date and not 03÷01÷2019. In this context Access requires you to supply a date in the mm/dd/yyyy form.)

For a date that does not appear in the table DLookup returns null. Not necessarily a helpful result.

In Microsoft Excel, it's VLOOKUP function allows you to avoid a null result by instructing the function to return the nearest matching result.

So what's needed is a function that's akin to VLOOKUP. But there's an additional requirement, explained in the next section.

An Additional Requirement

The table of body weights, above, is take from a database I've created for monitoring a person's diet. The database records the food the person has eaten and compares their daily intake of nutritional components — carbohydrates, sugars, fat, protein and so on — with recommendations for a healthy diet based on their body weight.

In using this database the person isn't required to weigh himself every day. The table above shows that weighing was made on reasonably frequent occasions and weight variations were not large.

In the database used by another person, weighings were taken place every two to three weeks. Because she was on a weight-loss programme, recorded weights varied a lot. Neither Access's DLookup nor Excel's VLOOKUP are fit for purpose when required to return a body weight for a date for which no weight was recorded.

Hence the lookup function I've created returns the person's weight for any date, even for one for which a weight is not recorded.

The DMW Lookup Function

The function described below is aimed at the data in tblBodyWeight, illustrated above. Later in this article I'll explain how to adapt it for general lookup purposes.

As the function commences its loop through the records in the table, the following code deals with the condition that the date being looked up is later than any date recorded in the table. In this circumstance the function returns the latest recorded weight assigned as it is to the y! variable.

As the function continues to loop, the following code deals with the condition that the date searched for matches a date in the table, resulting in the function returning the weight for that date. (In this response the function behaves as Access's DLookup and Excel's VLOOKUP.)

For neither of the two conditions above satisfied, the function continues its search for the required date.

The Do While … Loop causes the function to locate the point at which the date supplied to it as the value of its argument falls between two dates in the table. At this point the function has gathered all the values it needs to apply to the general y = mx + c equation for a straight line.

In the function, the straight line has dates on its horizontal x-axis and weights on its vertical y-axis. This block of code assembles the values needed for the line to extrapolate a value for y!:

How to Run the Function

To execute the function, copy and paste the code into a module in your database's Visual Basic Editor.

Then in the Editor's Immediate Window input, for example, the following statement and press Enter (don't omit the leading question mark):

A Full Blown Lookup Function

The fnDmwVLookUpByDate function, above, is restrictive in its application: it's looking up a date in a particular table, the details of which are buried in the code. If you needed to perform an alternative lookup, then you'd have to write another function.

What would be useful is a function that performs the lookup on any table or query, which is what I attempt to achieve with the dmwVLookUp function below.

For the function to work, these conditions must apply to the data source upon which the lookup is to be performed:

• The field to which the lookup is applied must be of a numeric or data/time data type
• The field to which the function is going to pick out values must be of a numeric or data/time data type. This is because the function will seek to infer values for data points that are not included in the table or query data set. It can only do this with numeric data.

To furnish the function with the information it needs, it has these four arguments:

luField$ The field in the table or query against which the function is performing the lookup

valField$ The field in the table or query from which the function is returning a value, or inferring a value when there's no exact lookup match

luDataSource$ The name of the table or query upon which the lookup is to be performed

luValue! The value in the luField$ field that you're want the function to look up.

I've not tested this function across many data sets, so please test it carefully before commissioning it for serious use. And please let me know any circumstances under which it falls over.

Here's an example of a test for this function (all of it on the same line, if you're going to copy it):

It should give the same results as my fnDmwVLookUpByDate function.

Data Type Identifier Characters

In the code above I've used characters in the composition of names for variables to identify the type of data that gets assigned to each of those variables. The formal name for one of these characters is identifier type character, each of which is tacked to the end of the name used for the variable.

These are the data type identifier characters recognised by VBA:

Note that there is no identifier for the date data type. That's why variables to which dates are assigned are dimensioned in this way:

Dim x1 As Date, x2 As Date

Which raises the question as to why does the dmwVLookUp function work when variables intended to hold dates are dimensioned in this way:

Dim x1&, x2&

The answer is because Access stores a date as a whole number for its own internal use. When you subtract one date from another, the resulting number is the number of days between the two dates. To confirm, test out the following in your Visual Basic Editor's Immediate Window:

?DateSerial(2019,06,30)-DateSerial(2019,06,01)

You can find out the number that Access holds representing June 1st 2020 or representing today's date:

?CLng(DateSerial(2020,06,01))

?CLng(Now())

The CLng coercion function nurses Access out of its habit of showing dates as dates, and into the corresponding numbers of its internal calendar.

To learn of Access's calendar's day one:

?CDate(1)

?Format(1,"yyyy-mm-dd")

And of the date 1000 days before day one:

?Format(-1000,"yyyy-mm-dd")

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —