Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
DLOOKUP is not returning a value - ms/access 2013
Posted on 2014-12-28
I am running a query that needs to compute a value based on one external table. The Table, named "__parametrosAAP", contains 3 fields for this purpose. A minimum value, a maximum value and a key value, named, respectively, [PopAlvoInf], [PopAlvoSup] and [ClasseAAP].
Depending on a value on the query, [PopPNUD] I need to select a specific record from "__parametrosAAP" and put it in the [ClassePNUD] column.
Thus I have the following query formula:
ClassePNUD: DLookUp("[ClasseAAP]";"__p
But, no matter how, it is always returning null values, in spite the table being correctly filled.
The query is named "sustenta 02 prep" and I am sending the database, just in case.
Can anyone help?
joao
sireshDB-11.accdb
Depending on a value on the query, [PopPNUD] I need to select a specific record from "__parametrosAAP" and put it in the [ClassePNUD] column.
Thus I have the following query formula:
ClassePNUD: DLookUp("[ClasseAAP]";"__p
But, no matter how, it is always returning null values, in spite the table being correctly filled.
The query is named "sustenta 02 prep" and I am sending the database, just in case.
Can anyone help?
joao
sireshDB-11.accdb
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
-
4
8 Comments
A Post scriptum -
I did replace, i the formula, the [PopPNUD] by a number (1050) and got the error "data type mismatch", a bit stupid as all values are number. The text was like:
ClassePNUD2: DLookUp("[ClasseAAP]";"__p
I did replace, i the formula, the [PopPNUD] by a number (1050) and got the error "data type mismatch", a bit stupid as all values are number. The text was like:
ClassePNUD2: DLookUp("[ClasseAAP]";"__p
Try
ClassePNUD: DLookUp("[ClasseAAP]";"__p
ClassePNUD: DLookUp("[ClasseAAP]";"__p
I checked your database and you do not have any field called "[PopPNUD]".
If you want to use the 1050, please do the following:
ClassePNUD2: DLookUp("[ClasseAAP]";"__p
The problem is where you put the "
Abracos
If you want to use the 1050, please do the following:
ClassePNUD2: DLookUp("[ClasseAAP]";"__p
The problem is where you put the "
Abracos
Hi fabricio -
As I am scratching my head, it suddenly almost started to work. I don't want to use the 1050 - that was only a try to check what was wrong.
The table "__parametrosAAP" contains the min and max values. The query "sustenta 02 prep" contains the field [PopPNUD]. Pleas check below.
When I run the query it now selects one of the 6 options. But always the same option (sigh) -> "Tipo I" so it looks that something is really wrong with the dlookup itself.
Actually, I did another try, now on the last column, replacing the [PopPNUD] field by the fized value "3500". This should yield the result "Tipo IV" and not "Tipo I" as it is!!!
I am uploading the database again so you can check.
joao
sireshDB-11.accdb
As I am scratching my head, it suddenly almost started to work. I don't want to use the 1050 - that was only a try to check what was wrong.
The table "__parametrosAAP" contains the min and max values. The query "sustenta 02 prep" contains the field [PopPNUD]. Pleas check below.
When I run the query it now selects one of the 6 options. But always the same option (sigh) -> "Tipo I" so it looks that something is really wrong with the dlookup itself.
Actually, I did another try, now on the last column, replacing the [PopPNUD] field by the fized value "3500". This should yield the result "Tipo IV" and not "Tipo I" as it is!!!
I am uploading the database again so you can check.
joao
sireshDB-11.accdb
Ok... I believe I found the solution...
try the following:
ClassePNUD2: DLookUp("[ClasseAAP]","__p
Now please sit before you read the following lines:
In you table "__parametrosAAP" the fields "PopAlvoInf" and "PopAlvoSup" have conflict information. Check it:
PopAlvoInf PopAlvoSup
0 1000
1001 1500
2001 2000
3001 3000
3001 5000
5001 100000
I understand "inf" means the minimum number and "Sup" means the maximum number, therefore you cannot have 2001 as minimum and 2000 as maximum??? Also you do not have any range covering from 1500 to 2000. Please check all the ranges ans make sure they cover your needs.
Abracos
try the following:
ClassePNUD2: DLookUp("[ClasseAAP]","__p
Now please sit before you read the following lines:
In you table "__parametrosAAP" the fields "PopAlvoInf" and "PopAlvoSup" have conflict information. Check it:
PopAlvoInf PopAlvoSup
0 1000
1001 1500
2001 2000
3001 3000
3001 5000
5001 100000
I understand "inf" means the minimum number and "Sup" means the maximum number, therefore you cannot have 2001 as minimum and 2000 as maximum??? Also you do not have any range covering from 1500 to 2000. Please check all the ranges ans make sure they cover your needs.
Abracos
Gee!!!!! It works perfectly! And thanks for the table correction!!!!!!
From what I understood, what you did was to always make the original table/query EXPLICIT. Is it?
T H A N K S A L O T !!!!
From what I understood, what you did was to always make the original table/query EXPLICIT. Is it?
T H A N K S A L O T !!!!
I did 2 important changes:
1-) Changed the calculated field name [PopPNUD] by its formula in the [ClassePNUD2]. You cannot use a calculated field name in a different field in the same query you are creating the original calculated field.
2-) The DLookup works with string parameters, therefore I had to concatenate a string in the "where" clausure. If you look in details you will realize this part is concatenated by &. For example the "Int(" & [CenarioConsumo].[Proj_2] & ")" will concatenate the string "Int(" with the value in [CenarioConsumo].[Proj_2] with the string ")" creating something like "int(1234)"
I hope it clarifies how the solution was reached.
Abracos
1-) Changed the calculated field name [PopPNUD] by its formula in the [ClassePNUD2]. You cannot use a calculated field name in a different field in the same query you are creating the original calculated field.
2-) The DLookup works with string parameters, therefore I had to concatenate a string in the "where" clausure. If you look in details you will realize this part is concatenated by &. For example the "Int(" & [CenarioConsumo].[Proj_2] & ")" will concatenate the string "Int(" with the value in [CenarioConsumo].[Proj_2] with the string ")" creating something like "int(1234)"
I hope it clarifies how the solution was reached.
Abracos
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 17 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.