Solved

Vlookup in Access

Posted on 2014-03-17
4
241 Views
Last Modified: 2014-03-21
Good Day Experts,
I have never executed a Vlookup in Access and was wondering if I could get some assistance.
I have two tables FACMatch and CSWMatch, I would like to compare or match any cert num in FACMatch with cardholderid in CSWMatch.  I also need to remove the last two digits of the cardholderid before performing the Vlookup.  For your review I have attached the accdb. Thank you for any assistance you can provide.
CSWMatch.accdb
0
Comment
Question by:Beeyen
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The equivalent function in Access is named DLookup().  However, in most cases a join is more efficient.
0
 

Author Comment

by:Beeyen
Comment Utility
Could you be more specific please? Thanks
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
select tbl1.fldA, tbl2.fldb
From tbl1 Inner Join tbl2 ON tbl1.fldA = tbl2.fldA;


select tbl1.fldA, DLookup("fldb", "tbl2", "fldA = " & tbl1.fldA)
From tbl1;

The first option is much more efficient than the second.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
However, in most cases a join is more efficient.
Not to mention that it would also be more portable (considering that the MS SQL Server topic was included in this thread).
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now