Solved

dlookup access 2010

Posted on 2013-02-04
17
874 Views
Last Modified: 2013-02-10
Hi,

I have have a dlookup which always returns the same value even when the selection criteria changes.

attached
1. the dlookup command in the data source control box
2. the first returned value; text 29 is the returned value; text 34 is the criteria value
3. same as 2 but notice that the reurned value stays the same even though the criteria changed
4 data table from which the dlookup is selecting values

some company identifying info has been removed. Why? because I'm weird that way.

Thanks
AEN
1-DLookup-syntax.jpg
2-DLookup-return-1.jpg
3-DLookup-return-2.jpg
4-DLookup-Table-values.jpg
0
Comment
Question by:allfredeneuman
  • 8
  • 3
  • 2
  • +2
17 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
ID: 38851178
Your Dlookup() syntax is a bit off.  Right now, literally your saying _competency must equal "_competency"

 You want the value in that control/field, so:

=Dlookup("leadership_factor","competency_definition","_competency=" & Me._competency

Jim.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38851186
Try changing your criteria to this:

"_competency =  '" & [_competency] & "'"
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 38851197
You may need:

=Dlookup("leadership_factor","competency_definition","_competency='" & Forms!yourFormName.[_competency] & "'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38856006
I am just curious about that odd name:
__competency

..are you sure this is the correct spellling and you did not leave off the prefix...?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38856045
Jeff,

If you look at image #4, it appears that "_compenency" is the suffix, as something appears to have been whited out, just not sure what it was.

Dale
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38856166
Oh,
OK
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38856203
Methinks Alfred E. Neuman is just being prudent in what he's posting (which is refreshing to see :-)  )
0
 

Author Comment

by:allfredeneuman
ID: 38857177
thank your suggestions. You are right about the syntax and the prefix to competency.

I now have the the dlookup syntax correct but it returns 4 records (all the same) - one for each record in my page query I presume. I tried running this look up with raw data for _competency. That is, I have selected _competency from one of the four records put the literal value in the lookup and I got 4 records back. The competency definition table only has one value for the particular _competency.  So now I need to know how to get back only one record. I seems like the dlookup does a left outer join between record in query and competency definition. I wonder if I put a select clause in there specifying an inner join. Any ideas? I'm running out of time on this.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:allfredeneuman
ID: 38857230
... if I use [me._competency], I get an error. I have to use [_competency]

returning multiple records is the problem. I've used dlookup in forms and have not a problem with returning multiple values.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38857363
A couple of points...

"Me" is a VBA prefix.  It will work in your VBA code, but not in property sheets or directly in queries.

Also, DLookup will return a single value - the first found or NULL if none are found

The query behind your form is controlling the number of records you are seeing.

Another way of doing this may be to join this extra table into your query, something like this:

SELECT Tablea.*, tableb.[leadership_factor] 
FROM  Tablea INNER JOIN tableb ON tablea.[_competency] = tableb.[_competency] 
WHERE tablea.[_competency] = Forms!YourFormName![_competency]

Open in new window


That's a general idea... however it is much easier to work with your syntax, names etc and we can easily get much closer to the syntax you need if you post code snippets (edited for sensitive info as needed) rather than images (which we can't copy/paste from)
0
 

Author Comment

by:allfredeneuman
ID: 38857420
more info

I looked at the report again. I noticed that the problem only happens for the first page or record of the report. The remaining 3 pages/records are fine. I inherited this report. It seems there is some property setting I am not aware of. I think I will delete this field and put it on the report again.

I do have a query for the report as mentioned above but when I use the field from the report query in the control box, I still get 4 rows of data. When I run the query by it's self everything is fine.

PS It is sometimes useful to write these things out. It seems I start to get ideas and solutions as I write.

PS thanks for the tip on using me.

back to researching
0
 

Author Comment

by:allfredeneuman
ID: 38857431
I noticed that when I did replace the field it 'looked' like it worked until I applied yes to can grow and can shrink. The first page/record showed the field repeated 4 times and the remaining 3 pages/records are fine. I think I am missing some property definition.
0
 

Author Comment

by:allfredeneuman
ID: 38857536
one more thing I noticed.... when I report the table to Excel. The paricular competency has the definition repeated in the competency definition cell the magical 4 times. I believe this is my problem.   more in a few mins. ....
0
 

Author Comment

by:allfredeneuman
ID: 38857560
Yes that was the problem. I do not know how it got repeated in the cell like that. This issue is closed. Thank you for your suggestions and input. I guess I award the points to myself and the people who suggested the syntax issue sine that was an actual programming problem that had to be fixed.
0
 

Author Comment

by:allfredeneuman
ID: 38861835
Thanks mbizup. I'll award the points to the people who indicated I had a syntax problem -AEN.
0
 

Author Closing Comment

by:allfredeneuman
ID: 38874734
Thank you all for your help. The syntax suggestions got me to looking at the date which was the real problem!

AEN
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

16 Experts available now in Live!

Get 1:1 Help Now