Solved

dlookup returns wrong entry

Posted on 2011-03-24
6
465 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 76
Migrating Macro Menu’s From Access 2003 to Access 2013 2 28
VBA name newly created sheet 4 23
SetFocus doesn't wait for input 14 31
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 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