Lookup function in access Results

I am using Access 2000 and am wanting to perform an action that pulls information from one table and updates another based on a number keyed. Effectively I am looking for the equivilent of Excel’s VLOOKUP action in Access.

I have a table named “Store Database” which details all store numbers and which region they are in. There are 7 regions and 3000 stores. Store number is the unique identifier. And this field appears on both tables.
There is then a table called “Master Calls” which is populated using a form where helpdesk operators key into fields and this populates the table.

What I am looking for is when the store number field is entered on the form the region field automatically populates from the other table.

Please can anyone help me with this or let me know if this is even possible. I am very rusty on my access skills!!
Sorry if i have posted in the wrong forum - i wasnt sure if this is classed as a table query or a form query - Please let me know if this is in the wrong place and i will repost!!

Let me just first preface this by saying I am relatively new to access. Used it many years ago (version 2000) but have recently picked it back up again (now version 2010) so my database skills are a little rusty. Here's the situation I am trying to rectify and would love to get some perspective on it. I have a table of employee hours by date ("Employee Hours"). I also have another table which includes the start and end dates of each payroll period ("Payroll Calendar"). I would like to assign the employee hours to the corresponding payroll period in the "Employee Hours" table. If I were using excel I would have created a forumula that looks something like this...



I am using a database to track student test scores. In this database raw scores are entered, but I would like to be able to calculate the scale score through looking up the scale score in another table.

Right now I have a table designed for entry of raw scores and a static table used just keep track of how to convert the scores to scale scores. I would like to automate the calculation of scale scores.

For example a student score of 28 on test form A is entered into a the raw test score table; I would like to have a scale score field created automatically through looking up the score in the static scale score table.

I know how to do this in Excell using VLOOKUP, but cannot figure this out in Access. Can someone help me or point me to a place where I can learn more about the lookup function in access



I've converted an Excel sheet to Access and am now trying to generate reports. In Excel, I used the LOOKUP command to get the data in a certain cell, determine if it was between a certain value range, and then return another value based upon the results.

For example, I had: =LOOKUP (A1, {0,5,10},{1,2,3})
So if the value of cell A1 was between 0 and 5, a 1 was placed in the cell where the formula resided.

I need similar functionality for Access reporting. Essentially, I need to look at the value of one of the fields that comes from the query and if it's between certain values, have another text field on the report that's filled in with pre-defined values. Is this possible?


I am new Access user,

I have a single table database, with an entry form and a need a very specific lookup functionality. I need to enter a serial number and have it return the exact match if it exists, AND 5 numbers above and five numbers below. The exact match should be highlighted in some way. if there is NOT an exact match, It needs to return the 10 closest records. Again five above and five below.

Can you suggest some sample codes.

Your help is highly appreciated


Hi everyone,

I have a Lookup field in Table 2 that displays some text (Steel, Stainless Steel or Aluminum) stored in Table 1. I (now) know that the Lookup field is actually storing an ID Number from Table 1 .. and thus ran into some trouble trying to use the text in a Record Set criteria:

Material = "Steel"
WHERE Material = '" & Material & "'

Because I'm actually saying "Steel" = 3 (or something similar)

I have read access.mvps.org/access/lookupfields.htm and I gather that I should go back and remove the Lookup fields to save myself even more trouble in the future.. but how do I "replace" the functionality it gave me? I was taught it is good database design to not repeat data.. as in if I had to change the name of Steel in Table 1, I'd have to remember to also do it in Table 2.. Hope this makes sense and thanks in advance for your wisdom!


Hi There,

I have recently upgraded from Office XP to Office 2007. I had an Access database which worked fine in Access XP. In it I had a table linked to an Excel spreadsheet. When I entered data on an access form, it updated the Excel spreadsheet [source document] accordingly. This does not seem to work in Access 2007. The linked table option now does not permit data entry or amendement. The other "Get external data" options create a table in Access which also does not update my source Excel document. The idea is to enter data in Access using a form. This data is placed in an excel spreadsheet. Excel gets some lookup values, and then enters these in a Word mail merge. Without the functionality of a linked table and data entry, I cannot produce new reports.

Can anybody help please?


Mark M.

Hi, I am fairly new to ACCESS. I understand the basics, thus i am a novice user.

I am trying to create a new database for managing booking and storing info on various artists.
In my main table where bands' info will be stored I am trying to make data input as efficient as possible and to ensure consistancy.

So, it leads me to ask this question... regarding inputing info of bands' location.
I want to use a lookup table to enter data for:
1 Country (Canada/US),
2 Province/State
3 Cities?

1 Whats the best way to organize this?
2 Do i have to create seperate tables for each country and province? Or any kind of suggestion would greatly appreciated.

Furthermore, when entering data on the form, for instance if i select:
Canada --> then I'd want only Canadian Provinces to show in the following field ---> then i select Ontario --> then i'd like only ontario cities to show in the next field....
3 What would require for me to create this kind of thing... Not sure if there are 'if' functions in ACCESS.

Could someone please guide me towards the light?

Hi all,

Sorry for such a Newbie question but....
I'm trying to find the equivalent of Filemaker's Lookup function within Access. To quote the Filemaker help page:

"A lookup copies data from another table into a field in the current table. After data is copied, it becomes part of the current table (and remains in the table from which it was copied). Data copied to the current table doesn't change automatically when the data in the other table changes.
To establish a connection between tables for a lookup, you create a relationship. Then you define a lookup to copy data from a field in the related table into a field in the current table."

The requirement for this is for an invoicing database where we need to copy a customer's invoice and shipping address into the invoice record so that, if the customer changes address, the old orders retain the actual address that was shipped to rather than be updated to whatever the customer's current address is. The same applies to many other things that fluctuate over time such as promotion discounts and list prices. When these change, looking back through old orders will give different prices than were actually invoiced. This must be a very common situation.

I've searched high and low on the web for an answer but so far have come up with nothing. A workaround might be to create the relationship then, when the customer ID (or product code etc) is changed, fire off a macro that copies the data from the related fields in the second table into the appropriate fields within the current table. If the Filemaker Lookup functionality is already present within Access it would be much neater to use that instead.

Any ideas?

Many thanks,
- Andy

Hi I am a newbie to access and would like to know how to write a query to run a simple vlookup functions between these two:
Tabel1 : has 200,000 rows of data. Has the fields to be looked up- "Serial_Number" and "Ship_Date"

Table2: has the field :"Serial_No"

I want table3 to Lookup Serial_No (tabel2) in the Serial_Number in table1 and return the corresponding row value of Ship_Date


I need to do two functions in Access which would be very simple in Excel. First one is lookup and the other is Sumif.

I have two tables UW and Clm (representing Underwriting and Claims respectively).

UW table contains following three columns:
1: ID (primary key)
2: Insured
3: Amt (currently empty)

Clm table contains the following columns:
1: ID (linked to ID in UW table. Can contain duplicate values)
2: Amt
3: Insured (currently empty)

First thing which I need to do is fill in UW.Amt with sum of Clm.Amt where ID is same. Eg. UW.ID = 1, the value in UW.Amt should be = 8,000. I tried Sum function in Update statement with either INNER JOIN or WHERE UW.Id = CLM.ID but neither are working.

Second thing which I need is to lookup the Insured name from UW table to Clm table by matching the ID numbers.

Like I said both these functions are very simple, however, I am getting stumped at it. Would appreciate if someone could create the update query in the attached file.

Thanks in advance for the help.

Best regards
Hatim Attached Files Database1.accdb (480.0 KB, 2 views) Reply With Quote 07-11-2011, 10:54 AM #2 jzwp11 VIP Windows 7 64bit Access 2010 64bit Join Date Jun 2010 Location Dayton, OH Posts 2,892 Welcome to the forum!

In relational databases, calculated values like sums are generally not stored in a table, you only store the individual data points that are used to get the sum. With that said, there would be no need for the Amt field in the UW nor would there be a need to run an update query. Also, you do not need to repeat data in one table that is already in another joined table. So you do not need the insured field in the Clm table.

You can accomplish what you are after with a totals query (air SQL text, not tested).

SELECT UW.ID, UW.Insured, Sum(Clm.Amt) as SumOfClaims

Sorry that I could not do this with the database you posted, I only have Access 2003 here at work and are unable to open a 2007/2010 file.

I have attached screen shots from the database that I need help with.

What I am trying to accomplish is in the Form "Assets" that was created from the Table "Assets". I want to populate the Validation Protocol field (below the blue line) and have all of the other information above the line (8 fields) populate automatically. The information that needs to go in these 8 fields is located in the "Asset List" table. How can I add this information automatically based on the entry of the Validation Protocol number?

The reason I need this is because when I add more protocols in the future with the same number, I dont want to have to type all of the information in each time. What will differentiate each record in the form will be the "Vol" field (volume). Also, in the future, I will be adding new assets into the "Asset List" table with each asset having its own protocol number (ie, the next asset will be VP3502, and so on).

I have been searching online for an answer for more than a week, but I cant seem to find out how. Is DLookUp the best way to do this or another way?

Someone, PLEASE HELP!!! I am not VB savy, so I am trying to use similar functions that are used in Excel.


Hi. I have four separate tables of product groups, i.e. Food Products, Non-Food Products, Meat, Produce. Each of these tables have a primary key that is a unique ID#. For instance, within the Food Products table this would be FP01, FP02, etc. In Meat would be M01, M02, etc. Each of these tables and products has a OurPrice field and a OurCost field.

We are using Quickbooks POS software, which exports our end-of-day report to an Excel file. This Excel file contains the ID# of each product (which matches our Access database) and the price of the product which matches the OurPrice field of our Access database tables. It does not contain the cost of the product, as the POS software is not capable of inputing this.

So, we import the end-of-day report to a new Access database table. What I need to know is how to use the Lookup function in this table to lookup the cost of each of the listed products from our other tables.

I've created a relationship between each of our tables with the ID#. Also, each of the tables has the ID# as the primary key.

I don't know where to go from here as each lookup only looks up from one table, not from multiple (do I need to do separate queries on each table???)...

Anyone have suggestions? Thanks for your time!

BIG-TABLE contains 1,000 detail records of 10 fields each.
LOOKUP-TABLE contains maybe 50 records of 1 field each.
ID is the field common to both tables, and is the primary key in BIG-TABLE.
Lookup-Table is named “FIRST” (the First subset of records to be printed).
SUBSET-QUERY drives BIG-REPORT, where the ID lookup succeeds,
because “FIRST” is entered to ‘criteria’ cell in SUBSET-QUERY for field ID.
The title “FIRST” is hard-wired in the header of BIG-REPORT (report title).

But I need to print numerous unique record sets from BIG-TABLE.
So I will create many LOOKUP-TABLE(s), named “SECOND”, “THIRD”, etc,
and each next Lookup-Table must be cycled through SUBSET-QUERY.
But I do not want to hand-edit ‘criteria’ to “SECOND” in SUBSET-QUERY,
nor do I want to hand-edit the BIG-REPORT header to “SECOND”.

Rather, I want SUBSET-FORM to accept the parameter “SECOND”;
pass that variable to the ‘criteria’ cell in SUBSET-QUERY for the ID field,
and pass that variable to the header of BIG-REPORT (to label next report).

Can these two properties be exhanged dynamically?

I may redesign:
Create (1) lookup table with columns FIRST to FIFTY by 1,000 records.
Check-boxes would flag records to print (yes/no), and
each report would be looked-up by column name.
But I would still have to pass a variable to the lookup function,
and would still want a way to switch out the report header.

I use Access-2000 and have little experience with code.

I am self taught in access, and am trying to convert a very nested ‘IF’ statement to a function. This function would be used inside a query.

The user will be prompted to enter a year (ie 2011) and an ID (ie.2). Depending on the selection made, the count information could come from one of four tables. Also, to avoid hard coding the years inside the function, I created a lookup table. This way, when the years change, I will only need to change in one place.

2010 CFY 0
2011 1FY 1
2012 2FY 2

This is the nested IF statement, which does return the correct results.

IIf([lkupFY]![FYID]=0,DLookUp("Count"," CEnrolls","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=1 And [C_IC]![TypeSchl]='A',DLookUp("Count "," ReqRPM_ReClass","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=1 And [lkup DS]![ID]=1,DLookUp("Count"," C_Count ","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=1 And [lkup DS]![ID]=2,DLookUp("Count","C_Count","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=2 And [C_IC]![TypeSchl]='A',DLookUp("Count","C_Count","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=2 And [lkup DS]![ID]=2,DLookUp("Count","ReqPom","[CID] & '' & [FY]=""" & [LKUP] & """"),
IIf([lkupFY]![FYID]=2 And [lkup DS]![ID]=1,DLookUp("Count","C_Count","[CID] & '' & [FY]=""" & [LKUP] & """"),'x')))))))

This is my attempt at converting the ‘if’ statement to a function. Keep in mind this is my very first attempt at any function.

Public Function GetReq5(FY As Integer, ID As Integer) As Integer
Dim req As Integer
Dim lkup As Variant
Dim TypeSchl As Variant
Dim FYID As Integer
Dim CID As Variant

If FYID = 0 Then
req = Nz(DLookup("Count", " CEnrolls ", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year choose is the current year then select count of students enrolled in the course from the CEnrolls table
ElseIf FYID = 1 And TypeSchl = "A" Then
req = Nz(DLookup("Count", " ReqRPM_ReClass", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 1 and the course is an A course then select the number of students planned from the ReqRPM ReClass table
ElseIf FYID = 1 And ID = 1 Then
req = Nz(DLookup("Count", " C_Count ", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 1 and the Source id = 1 then select the number of students planned from the C_Count table
ElseIf FYID =1 And ID = 2 Then
req = Nz(DLookup("Count", "C_Count", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 1 and the Source id = 2 then select the number of students planned from the C_Count table
ElseIf FYID = 2 And TypeSchl = "A" Then
req = Nz(DLookup("Count", "C_Count", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 2 and the course is an A course then select the number of students planned from the C_Count table
ElseIf FYID = 2 And ID = 1 Then
req = Nz(DLookup("Count", "C_Count", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 2 and the Source id = 1 then select the number of students planned from the C_Count table
ElseIf FYID = 2 And ID = 2 Then
req = Nz(DLookup("Count", "ReqPom", "[CID] & '' & [FY]=""" & [lkup] & """"), 0)
' if the fiscal year chosen is the current year + 2 and the Source id = 2 then select the number of students planned from the C_Count table
req = 999
End If

GetReq5 = req

End Function

Running the following in the intermediate window returns a 0. In fact, it doesn’t matter which FY is entered, 0 is returned.

Okay, so I'm using a form to launch a query and I need this query to set the criteria based on what is being viewed in the form. I've researched and found that in order to use a variable in a query, you have to pass it to a function.

Here's my code for the form that sets the variable:

	Private Sub cmdClassReport_Click()

Dim txtModelClass As String

'uses open form to lookup info in related table and set the variable
txtModelClass = Nz(DLookup("[fleetModelClass]", "tblFleetCopiers", "[fleetAutoID] =" & Me!locFleetIDAuto), 0)

GetModelClass txtModelClass

DoCmd.OpenQuery "qryCopiers"

End Sub

And here is my function:

	Public Function GetModelClass(ByVal txtModelClass As String) As String

'variable set in Sub used as value of function
GetModelClass = txtModelClass

End Function

Here is what I have as my criteria in my query:


I've verified that the variable (txtModelClass) gets set correctly in the Sub by displaying a message box but my query keeps returning blank.

What am I doing wrong?

I am using Access 2007.

I would like to create a function in a module for a text field, similar to the function created by Bob Larson on 10/12/2012 when answering a thread called "Default Value in Forms based on value in another table". It appears that he is creating a function for a numeric field.

I have a situation where I want to establish a function on a text field.

Also, is there a different way to do this without using the Lookup function? I have found that the Lookup function can slow down my database.

Any input/comments/suggestion would be greatly appreciated.

Hi, hope someone can enlighten me.... I have a table tblFines, with two fields NumberOfDaysLate and Fine

NumberOfDaysLate | Fine
7 | 0.3
14 | 0.6
21 | 1
28 | 1.5
56 | 3

(Sorry about the bad layout of the table above!)

I know that in excel if I use a vlookup function I can search for the value stored as DaysLate (see below) and the vlookup(dayslate, tblFines, 2) would return the fine.

I can't seem to get it to work like this in access - the dlookup function would appear to be what I need to use but I can't get it to work.. I have in the interim used a collection of IF statements like below.

Public Sub CalculateFines(DaysLate As Integer)
Dim FineDue As Double
FineDue = 0
If DaysLate >= 7 Then
FineDue = 0.3
End If
If DaysLate >= 14 Then
FineDue = 0.6
End If
If DaysLate >= 21 Then
FineDue = 1
End If
If DaysLate >= 28 Then
FineDue = 1.5
End If
If DaysLate >= 56 Then
FineDue = 3
End If
MsgBox DaysLate & " day(s) late with a fine of " & FineDue, vbOKOnly
End Sub

But I would ideally like to refer to the table so I can change the values or terms of the fine. The fines table is not related to anything and so on the form that this is called from I get stuck.

I have tried using:

Public Sub CalculateFines(DaysLate As Integer)
Dim FineDue As Double
FineDue = dlookup( [Fine], tblFines, [NumberOfDaysLate] = DaysLate)
MsgBox DaysLate & " day(s) late with a fine of " & FineDue
End Sub

But this keeps giving me an error on the tblFines part of the dlookup statement....

any help much appreciated by one and all... Is there actually a function that will work like vlookup and not have to find a specific value, ie. If 9 days late return the value for 7 in the table?

Thanks again


I am working with Access 97 and experiencing difficulty in 97 returned errors when trying to perform a left function on a returned memo field. Details are below. Has anyone experienced this and have a workaround for it? Do you know if this is a known error in 97?
The purpose of this query is concatenate a series of comments registered against a specific demand id in one field (fConcatFld) which works fine in 2003 but only works in 97 when the total length of all comments being concatenated are less than 256.
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate, Left(fConcatFld("tblDemandProgress","DemandNo;Dema ndDate","DemandProgress","String;DateTime",[DemandNo] & ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate"), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;

fConcatFld returns a single concatenated string to the query (details of fConcatFld below). Many of the concatenated strings returned by 'fConcatFld' are larger than '255', which means storing the comment in a text field would be inappropriate due to its length of 255 with a memo field better (storage of 2.5GB).
Access by default uses the data type of the field from which the comments are derived from (which in the database in this case is a text field) as the data type for the field the comments will be inserted into. To avoid automatic truncation of concatenated comments to the database comments field type (225 length) we have converted the field to a memo by performing a union with a temporary table (tblTemp) which contains an empty comment field of type memo.
This works successfully with concatenated comments of up to 4000 in Access 2003 but only with concatenated comments of total length of up to 255 in 97. When this query is executed in 97 the function executes, concatenating the first group of comments associated with a unique ID, but as soon as control is handed back to the query the following application error is thrown:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
When the argument in the 'LEFT' function (shown in query above) is reduced to


I am trying to play around with query but got stuck in fixing the bug.

Please see attachment. I am trying to link the color code from a table to another table which stores the definition of color code. However, it gives me duplicate results.

In Excel, this could be achieved by using the vlookup function. How do I do it in Access?

Thanks a lot!

Not finding an answer? Try a Google search.