dlookup returns wrong entry

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
João serras-pereiraAsked:
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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?
Anthony PerkinsCommented:
>>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.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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:


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


 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:


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


João serras-pereiraAuthor Commented:
it worked perfetly thanks!
João serras-pereiraAuthor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.