Solved

DLOOKUP is not returning a value - ms/access 2013

Posted on 2014-12-28
8
291 Views
Last Modified: 2014-12-29
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.

Can anyone help?

joao
sireshDB-11.accdb
0
Comment
Question by:jirdeaid
  • 4
  • 4
8 Comments
 

Author Comment

by:jirdeaid
ID: 40520487
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]";"__parametrosAAP";1050<=" [__parametrosAAP]![PopAlvoSup]  " And 1050>="[__parametrosAAP]![PopAlvoInf]")
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40520505
Try

ClassePNUD: DLookUp("[ClasseAAP]";"__parametrosAAP";"([PopPNUD]<= [PopAlvoSup]) And ([PopPNUD]>=[PopAlvoInf])")
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40520512
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]";"__parametrosAAP";"(1050<= [__parametrosAAP]![PopAlvoSup])  And (1050>=[__parametrosAAP]![PopAlvoInf])")

The problem is where you put the "

Abracos
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:jirdeaid
ID: 40520522
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.

screenshot

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!!!

screenshot 2
I am uploading the database again so you can check.

joao
sireshDB-11.accdb
0
 
LVL 8

Accepted Solution

by:
fabriciofonseca earned 500 total points
ID: 40520562
Ok... I believe I found the solution...

try the following:

ClassePNUD2: DLookUp("[ClasseAAP]","__parametrosAAP","((Int(" & [CenarioConsumo].[Proj_2014] & "*" & [_Sustenta 01 Prep].[TxAcum] & "/10+0.5)*10)<= [PopAlvoSup])  And ((Int(" & [CenarioConsumo].[Proj_2014] & "*" & [_Sustenta 01 Prep].[TxAcum] & "/10+0.5)*10)>=[PopAlvoInf])")

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
0
 

Author Closing Comment

by:jirdeaid
ID: 40520589
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 !!!!
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40521899
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
0
 

Author Comment

by:jirdeaid
ID: 40522328
and re-THANKS!!!!!!!
abraços tb (de Portugal)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

821 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