Solved

dlookup returns wrong entry

Posted on 2011-03-24
6
466 Views
Last Modified: 2012-05-11
I have a small database and, in a form, I do need to obtain a value in a table that dependes on the combination of  2 entries.
I am using the Dlookup funcion as per below:

=DLookUp("[EstabelecimentoBancario]";"[ContaBanco]";"[NumeroProjeto]='" & [FormCodigoProjetoFED] & "'" And "[NomeBeneficiario]='" & [FormNomeBeneficiario] & "'")

and I am using this for 3 fields in the form as I need to obtain 3 different values.
However, in the form, I am consistently getting , for the first field, a wrong return, and for the other, it is returing "#error"
I am attaching the database

can anyone help me?  formulariosFEDON.accdb
0
Comment
Question by:jirdeaid
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35206237
try this


=DLookUp("[EstabelecimentoBancario]";"[ContaBanco]";"[NumeroProjeto]='" & [FormCodigoProjetoFED] & "' And [NomeBeneficiario]='" & [FormNomeBeneficiario] & "'")

where did you place the codes that you are using?

are these the names of the controls in the form?

[FormCodigoProjetoFED],  [FormNomeBeneficiario]

what is the name of the form?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35206245
>>Zones: MS SQL Server, Microsoft Applications, Microsoft Access Database<<
This has nothing to do with SQL Server.  Please request that the MS SQL Server zone be removed.
0
 
LVL 57
ID: 35206254
Without looking at the DB:

1. You need to use coma's as delimiters between the arguments, not semi-colons:

=DLookUp("EstabelecimentoBancario","ContaBanco","[NumeroProjeto]='" & [FormCodigoProjetoFED] & "'" And "[NomeBeneficiario]='" & [FormNomeBeneficiario] & "'")

2. and this:

[FormCodigoProjetoFED]

  Does not look right.  If this is in code in a form, it should be in the format of:

  Me.<myControlName>

 So it might look like this:

=DLookUp("EstabelecimentoBancario","ContaBanco","[NumeroProjeto]='" & Me.ProjetoFED & "'" And "[NomeBeneficiario]='" & Me.Beneficiario] & "'")

 Where ProjetoFED and Beneficiario would be the control names.

  Ifthe Dlookup statement is being used as the controlsource of a control, then it should be in the format of:

 Forms![<myControlName>]

=DLookUp("EstabelecimentoBancario","ContaBanco","[NumeroProjeto]='" & Forms![FormCodigo]![ProjetoFED] & "'" And "[NomeBeneficiario]='" & Forms![FormCodigo]![Beneficiario] & "'")

JimD.

0
 

Author Closing Comment

by:jirdeaid
ID: 35207835
it worked perfetly thanks!
0
 

Author Comment

by:jirdeaid
ID: 35207872
Just a final note:

JDettman: thanks for removing for me; I ha wrongly classified it

also commas are not delimiters in my system. I can't tell why but the delimiters are semi-colons in Portugal althou my system is in english..
acperkinds: thanks for the alert



0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

856 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