Looking up values from an Access table is simple. Simulating the 'Range Lookup' functionality from Excel's VLookup formula is a bit trickier.
For those that aren't familiar with this, it allows you to look up the next smallest value to what you provided, and return any corresponding field from the table. Very useful for looking up things like currency exchange rates, tax rates, etc., where there might not be an entry for every day/income level/etc.
This code is based on a hard-coded solution I found at:
http://www.access-programmers.co.uk/forums/showthread.php?t=69080
Thanks to ByteMyzer for providing the concept, I've just made it into a flexible VBA function. -
-
-
Public Function AccessVLookup(strTable As String, strLookupField As String, _
-
varLookupValue As Variant, strReturnField As String, _
-
Optional strCriteriaField As String, Optional varCriteriaValue As Variant) As Variant
-
-
'Aaron Ringer 26 Feb 08
-
-
'Simulates the Excel VLookup function in Access, complete with Range Lookup argument.
-
'I've used it to look up currency exchange rates, to get the XRate active at the given date.
-
'It means that there doesn't have to be an entry on the given date, it'll find the next lowest value.
-
'Can be used to look up anything, as long as the lookup field contains numeric data.
-
'Returns zero if nothing found.
-
-
'strTable = Name of lookup table.
-
'strLookupField = Name of field to search.
-
'varLookupValue = Value to look for in lookup field.
-
'strReturnField = Field to return value from.
-
-
'Example:
-
'Gets the exchange rate for the given currency active as of today.
-
'dblExchangeRate = AccessVLookup("tblCurrencyExchangeRate", "EffectiveDate", Date, "ExchangeRate", "CurrencyID", lngCurrencyID)
-
-
Dim strSQL As String
-
Dim qdf As DAO.QueryDef
-
Dim rst As DAO.Recordset
-
-
strSQL = "SELECT "
-
strSQL = strSQL & "T1." & strReturnField
-
strSQL = strSQL & " FROM " & strTable & " AS T1 "
-
strSQL = strSQL & "WHERE T1." & strLookupField & "="
-
strSQL = strSQL & "(SELECT Max(T2." & strLookupField & ") "
-
strSQL = strSQL & "FROM " & strTable & " AS T2 "
-
strSQL = strSQL & "WHERE T2." & strLookupField & " <= "
-
strSQL = strSQL & "[LookupValue]"
-
-
If Len(strCriteriaField) > 0 Then
-
strSQL = strSQL & " AND [" & strCriteriaField & "]"
-
strSQL = strSQL & " = " & varCriteriaValue
-
End If
-
-
strSQL = strSQL & ")"
-
-
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
-
-
qdf.Parameters("LookupValue") = varLookupValue
-
Set rst = qdf.OpenRecordset
-
If rst.RecordCount > 0 Then AccessVLookup = rst.Fields(strReturnField)
-
-
rst.Close
-
qdf.Close
-
Set rst = Nothing
-
Set qdf = Nothing
-
-
End Function
-
I've spent a bit of time on it, but haven't thoroughly optimised it - if anyone can suggest any improvements, please let us all know.
Enjoy!
1 6765
I've found a flaw in the function which meant that, if there are several records with the same LookupValue, it might not return the right one.
Fixed function: -
Public Function AccessVLookup(strTable As String, strLookupField As String, _
-
varLookupValue As Variant, strReturnField As String, _
-
Optional strCriteriaField As String, Optional varCriteriaValue As Variant) As Variant
-
-
'Aaron Ringer 28 Jan 09
-
-
'Simulates the Excel VLookup function in Access, complete with Range Lookup argument.
-
'I've used it to look up currency exchange rates, to get the XRate active at the given date.
-
'It means that there doesn't have to be an entry on the given date, it'll find the next lowest value.
-
'Can be used to look up anything, as long as the lookup field contains numeric data.
-
'Returns zero if nothing found.
-
-
'strTable = Name of lookup table.
-
'strLookupField = Name of field to search.
-
'varLookupValue = Value to look for in lookup field.
-
'strReturnField = Field to return value from.
-
-
'Example:
-
'Gets the exchange rate for the given currency active as of today.
-
'dblExchangeRate = AccessVLookup("tblCurrencyExchangeRate", "EffectiveDate", Date, "ExchangeRate", "CurrencyID", lngCurrencyID)
-
-
Dim strSQL As String
-
Dim qdf As DAO.QueryDef
-
Dim rst As DAO.Recordset
-
-
strSQL = "SELECT "
-
strSQL = strSQL & "T1." & strReturnField
-
strSQL = strSQL & " FROM " & strTable & " AS T1 "
-
strSQL = strSQL & "WHERE T1." & strLookupField & "="
-
strSQL = strSQL & "(SELECT Max(T2." & strLookupField & ") "
-
strSQL = strSQL & "FROM " & strTable & " AS T2 "
-
strSQL = strSQL & "WHERE T2." & strLookupField & " <= "
-
strSQL = strSQL & "[LookupValue]"
-
-
If Len(strCriteriaField) > 0 Then
-
strSQL = strSQL & " AND [" & strCriteriaField & "]"
-
strSQL = strSQL & " = " & varCriteriaValue
-
End If
-
-
'Need this extra criteria, or may return value from other record with same value in strLookupField.
-
strSQL = strSQL & " AND T1." & strCriteriaField
-
strSQL = strSQL & " = " & varCriteriaValue
-
-
strSQL = strSQL & ")"
-
-
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
-
-
qdf.Parameters("LookupValue") = varLookupValue
-
Set rst = qdf.OpenRecordset
-
If rst.RecordCount > 0 Then AccessVLookup = rst.Fields(strReturnField)
-
-
rst.Close
-
qdf.Close
-
Set rst = Nothing
-
Set qdf = Nothing
-
-
End Function
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: NeoPa |
last post by:
VlookUp or Vertical LookUp is a function that enables Excel to ‘Find’ related items from within a range stored elsewhere. Elsewhere can mean another excel workbook on another PC, but can also mean a...
|
by: catlover30 |
last post by:
HI,
I need help with excel VLOOKUP! I am at a new job and really trying to solve this without asking anyone at work, since they seem th expect me to know this. What I am trying to acheive is the...
|
by: Widge |
last post by:
I have made a spreadsheet that will calculate the distance from a set of addresses to a specified postcode in Excel. I'm looking to move this to Access so I can start thinking about hooking it up to...
|
by: D.Stone |
last post by:
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmatically using TransferSpreadsheet, but to check that the file...
|
by: acarrazco |
last post by:
Hello, I am totaly new to VBA and I'm trying to modify a macro that was given to me but it doesn't seem to be working. I'm trying to extract data from three excel spreadsheets, put it into a combined...
|
by: sejal17 |
last post by:
hello
Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store...
|
by: sejal17 |
last post by:
hello
Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store...
|
by: John Brock |
last post by:
I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another...
|
by: Monusonu |
last post by:
Hi Expert,
I am trying to get the value from excel formula cell using POI. My code works fine for less complex formula cells, but fails or returns error code for complex formula cells.
Following...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |