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]";"__parametrosAAP";[PopPNUD]<=" [__parametrosAAP]![PopAlvoSup] " And [PopPNUD]>="[__parametrosAAP]![PopAlvoInf]")
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.
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]";"__parametrosAAP";1050<=" [__parametrosAAP]![PopAlvoSup] " And 1050>="[__parametrosAAP]![PopAlvoInf]")
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.
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)"
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
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