DLookup doesn't return right values.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating that the
syntax is incorrect, or that I have entered an operator without an operand. I
have tried using DLookup both directly in the form and in a Query on which I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing seems to
work. Please Help!!
 
It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] & """")
 
Hi. Thank you for the prompt response. I do have a habit of forgetting the
closing parenthesis, as I did when I was typing in my question. The program
always tells me when I've made that mistake and even when I fix that, it
still returns the same result regardless of the name of the test that is
entered.

Test is a text field. When I enter the argument as you have suggested, I get
a blank in the field where the test result should show.

Duane Hookom said:
It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] & """")


--
Duane Hookom
MS Access MVP

CharM said:
I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test
entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" =
"[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test
Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating that the
syntax is incorrect, or that I have entered an operator without an
operand. I
have tried using DLookup both directly in the form and in a Query on which
I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing seems
to
work. Please Help!!
Click to expand...
Click to expand...
 
Do you have a fields in [Test Results] named [Result 1] and [Test]?
Do you have a field [Test] in the record source of your form?
What happens if you press Ctrl+G to open the immediate window and enter:
? DLookup("[Result 1]" , "[Test Results]", "[Test] = ""Real Value of Test""
")

--
Duane Hookom
MS Access MVP


CharM said:
Hi. Thank you for the prompt response. I do have a habit of forgetting the
closing parenthesis, as I did when I was typing in my question. The
program
always tells me when I've made that mistake and even when I fix that, it
still returns the same result regardless of the name of the test that is
entered.

Test is a text field. When I enter the argument as you have suggested, I
get
a blank in the field where the test result should show.

Duane Hookom said:
It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] & """")


--
Duane Hookom
MS Access MVP

CharM said:
I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test
entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" =
"[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test
Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating that
the
syntax is incorrect, or that I have entered an operator without an
operand. I
have tried using DLookup both directly in the form and in a Query on
which
I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing
seems
to
work. Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.
Click to expand...

That's because this expression is searching for all those records
where the field named [Test] in the table [Test Results] is equal TO
ITSELF. You're not just comparing apples to apples, you're comparing
an apple to itself! Of course it's equal.

What's the context? Where are you using this Dlookup? Clearly you're
looking for a particular value of the field Test - what value of Test
are you looking for - the value from a Form? If so, what control on
the Form? What's the datatype of the field Test?

You way you want to "return a set of possible test results" - you are
aware, I hope, that DLookUp returns a single value, not a "set"?


John W. Vinson[MVP]
 
Hi,

To answer your questions, yes, I have fields in [Test Results] named [Result
1] and [Test]. [Test] is the primary key, and there are no duplicate entries.
And yes, I have a field [Test] in the record source of the form. I have tried
making it a combo box with the selections linked to the [Test] field in [Test
Results] to try to make sure the two fields have exactly the same entry, but
it didn't help.

When I select the control source on the form in which I am trying to use the
DLooup function, press control+G and type in the expression as suggested, it
returns the response: Null.

I should clarify that I am trying to use the Expression Builder and not
Visual Basic. I don't know how to use Visual Basic, so am a bit limited.

Thanks for your help with this.

Duane Hookom said:
Do you have a fields in [Test Results] named [Result 1] and [Test]?
Do you have a field [Test] in the record source of your form?
What happens if you press Ctrl+G to open the immediate window and enter:
? DLookup("[Result 1]" , "[Test Results]", "[Test] = ""Real Value of Test""
")

--
Duane Hookom
MS Access MVP


CharM said:
Hi. Thank you for the prompt response. I do have a habit of forgetting the
closing parenthesis, as I did when I was typing in my question. The
program
always tells me when I've made that mistake and even when I fix that, it
still returns the same result regardless of the name of the test that is
entered.

Test is a text field. When I enter the argument as you have suggested, I
get
a blank in the field where the test result should show.

Duane Hookom said:
It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] & """")


--
Duane Hookom
MS Access MVP

I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test
entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" =
"[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test
Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating that
the
syntax is incorrect, or that I have entered an operator without an
operand. I
have tried using DLookup both directly in the form and in a Query on
which
I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing
seems
to
work. Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
What happens if you open a new query based on [Test Results] and view the
sql entering this expression:

SELECT [Result 1]
FROM [Test Results]
WHERE [Test] = "Real Value of Test";

This assumes Test is a text type field.

Are you using lookup fields in your table design?


--
Duane Hookom
MS Access MVP

CharM said:
Hi,

To answer your questions, yes, I have fields in [Test Results] named
[Result
1] and [Test]. [Test] is the primary key, and there are no duplicate
entries.
And yes, I have a field [Test] in the record source of the form. I have
tried
making it a combo box with the selections linked to the [Test] field in
[Test
Results] to try to make sure the two fields have exactly the same entry,
but
it didn't help.

When I select the control source on the form in which I am trying to use
the
DLooup function, press control+G and type in the expression as suggested,
it
returns the response: Null.

I should clarify that I am trying to use the Expression Builder and not
Visual Basic. I don't know how to use Visual Basic, so am a bit limited.

Thanks for your help with this.

Duane Hookom said:
Do you have a fields in [Test Results] named [Result 1] and [Test]?
Do you have a field [Test] in the record source of your form?
What happens if you press Ctrl+G to open the immediate window and enter:
? DLookup("[Result 1]" , "[Test Results]", "[Test] = ""Real Value of
Test""
")

--
Duane Hookom
MS Access MVP


CharM said:
Hi. Thank you for the prompt response. I do have a habit of forgetting
the
closing parenthesis, as I did when I was typing in my question. The
program
always tells me when I've made that mistake and even when I fix that,
it
still returns the same result regardless of the name of the test that
is
entered.

Test is a text field. When I enter the argument as you have suggested,
I
get
a blank in the field where the test result should show.

:

It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] &
"""")


--
Duane Hookom
MS Access MVP

I am trying to use the DLookup function in Access 2003 to get a form
to
return a set of possible test results based on the name of the test
entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" =
"[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test
Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating
that
the
syntax is incorrect, or that I have entered an operator without an
operand. I
have tried using DLookup both directly in the form and in a Query on
which
I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing
seems
to
work. Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your willingness to help me out on this. I am sure all of this
is obvious to you, but I am new at trying to use DLookup and it isn't working
even though I enter the formulas exactly as they are shown in examples and
instructions from Microsoft Help and other posts on this website. Let me
start at the beginning:

I have two tables, [Test Results] and [Test Tracking]. [Test Results] has a
field [Test] that contains the names of tests, and a field [Result 1] that
contains a result for each test. [Test Tracking] also has a field [Test] and
a field [Result 1]. All fields are text.

I have constructed a form based on [Test Tracking] in which the person
entering data would use a combo box to select the specific test they want to
enter. The combo box is set to select its list of values from the [Test]
field in [Test Results]. I want the DLookup function to populate the control
source [Result 1] in the form with the value from the [Test Results] table
that corresponds to the [Result 1] value for the test entered. Should be
pretty straightforward, but nothing I have tried so far has worked.

If you read the rest of the discussion string, I believe you will see that
the issue of correctly identifying the domains of the comparative [Test]
fields has been addressed by Mr. Hookom's suggested syntax, however, I am now
getting Null values returned instead of the test result values.

If you have additional suggestions, I would be grateful.

Thanks.

John Vinson said:
I am trying to use the DLookup function in Access 2003 to get a form to
return a set of possible test results based on the name of the test entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.
Click to expand...

That's because this expression is searching for all those records
where the field named [Test] in the table [Test Results] is equal TO
ITSELF. You're not just comparing apples to apples, you're comparing
an apple to itself! Of course it's equal.

What's the context? Where are you using this Dlookup? Clearly you're
looking for a particular value of the field Test - what value of Test
are you looking for - the value from a Form? If so, what control on
the Form? What's the datatype of the field Test?

You way you want to "return a set of possible test results" - you are
aware, I hope, that DLookUp returns a single value, not a "set"?


John W. Vinson[MVP]
Click to expand...
 
Hi,

I tried your suggestion and kept coming up with blank results. So I started
completely over and reconstructed a new database. Now it seems to be doing
what it is supposed to. The language that is working is

=DLookUp("[Result 1]","[Test Results]","[Test] = """ & Forms![Test
Tracking]!Test & """")

I still don't know what was wrong with the original database, but it's
working now. Thank you so much for all of your suggestions. I learned a
bunch. Hopefully things will work now.


Duane Hookom said:
What happens if you open a new query based on [Test Results] and view the
sql entering this expression:

SELECT [Result 1]
FROM [Test Results]
WHERE [Test] = "Real Value of Test";

This assumes Test is a text type field.

Are you using lookup fields in your table design?


--
Duane Hookom
MS Access MVP

CharM said:
Hi,

To answer your questions, yes, I have fields in [Test Results] named
[Result
1] and [Test]. [Test] is the primary key, and there are no duplicate
entries.
And yes, I have a field [Test] in the record source of the form. I have
tried
making it a combo box with the selections linked to the [Test] field in
[Test
Results] to try to make sure the two fields have exactly the same entry,
but
it didn't help.

When I select the control source on the form in which I am trying to use
the
DLooup function, press control+G and type in the expression as suggested,
it
returns the response: Null.

I should clarify that I am trying to use the Expression Builder and not
Visual Basic. I don't know how to use Visual Basic, so am a bit limited.

Thanks for your help with this.

Duane Hookom said:
Do you have a fields in [Test Results] named [Result 1] and [Test]?
Do you have a field [Test] in the record source of your form?
What happens if you press Ctrl+G to open the immediate window and enter:
? DLookup("[Result 1]" , "[Test Results]", "[Test] = ""Real Value of
Test""
")

--
Duane Hookom
MS Access MVP


Hi. Thank you for the prompt response. I do have a habit of forgetting
the
closing parenthesis, as I did when I was typing in my question. The
program
always tells me when I've made that mistake and even when I fix that,
it
still returns the same result regardless of the name of the test that
is
entered.

Test is a text field. When I enter the argument as you have suggested,
I
get
a blank in the field where the test result should show.

:

It looks like in every case you are missing the closing paren.
If Test is numeric, try:
=DLookup("[Result 1]" , "[Test Results]", "[Test] = " & [Test])
If test is text
=DLookup("[Result 1]" , "[Test Results]", "[Test] = """ & [Test] &
"""")


--
Duane Hookom
MS Access MVP

I am trying to use the DLookup function in Access 2003 to get a form
to
return a set of possible test results based on the name of the test
entered
in the form. The expression
=DLookup("[Result 1]" , "[Test Results]", "[Test]" = "[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test]"
returns the same result no matter what test name is entered.

Typing it as
=DLookup("[Result 1]" , "[Test Results]", "[Test Tracking]![Test]" =
"[Test]"
or
=DLookup("[Result 1]" , "[Test Results]", "[Test] = [Test
Tracking]![Test]"
gets me #Error

Trying to use any other syntax results in error messages stating
that
the
syntax is incorrect, or that I have entered an operator without an
operand. I
have tried using DLookup both directly in the form and in a Query on
which
I
would then base the form and am having the same problem.

I know I must be doing something wrong in the criteria, but nothing
seems
to
work. Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top