Solved

Ms Access - DLookup into text field

Posted on 2008-10-03
1
515 Views
Last Modified: 2012-05-05
Hi,
I am having problems tryiing to populate my text box with a field from a query. I have tried everything. Basically I want to be able to put a value in my text box, on my form, using the DLookup function. I am looking up values from my query and get a data type mismatch error. My query is;

SELECT tbl_Historical_SPN_Trades.spn_name, tbl_Exclusion_Type.Exclusion, tbl_Historical_SPN_Trades.Date_Added, tbl_Historical_SPN_Trades.spn_id AS SPN
FROM tbl_Historical_SPN_Trades INNER JOIN tbl_Exclusion_Type ON tbl_Historical_SPN_Trades.[Exclusion Type] = tbl_Exclusion_Type.Exclusion_ID;

And My DLookup is

strSPNValue = txtSPN.Value  (Which will equal something like 0020397)
txtExclusion.Text = DLookup("[Exclusion]", "Sel_Historical_SPN_Lookup", "SPN = " & strSPNValue)

Now SPN is a text field in my table, as is Exlusion. I can not see why the error is occuring?
0
Comment
Question by:andyb7901
1 Comment
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22632560
Text values must be enclosed in single or double quotes. I use single quotes:

txtExclusion = DLookup("[Exclusion]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue & "'")

Also, don't set the .Text value ... in order to do that, your control must have the focus. Access isn't like other programming environments (like VB) ...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

14 Experts available now in Live!

Get 1:1 Help Now