DLOOKUP is not returning a value - ms/access 2013
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
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
and re-THANKS!!!!!!!
abraços tb (de Portugal)
abraços tb (de Portugal)
ASKER
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