Solved

dlookup access 2010

Posted on 2013-02-04
17
859 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

13 Experts available now in Live!

Get 1:1 Help Now