DLookup help

  • Thread starter Thread starter Stoneface
  • Start date Start date
S

Stoneface

I'm trying to do a DLookup with four fields, all string values. The following
works when the tblINFO fields are assigned specific values, but gives an
error (#Name?) when using the tblINFO field values:

DlookUp("[tblPARS_FIELD5]", "tblPARS_TEST", "[tblPARS_FIELD1] = '"&
[frmInfo_test]![tblINFO_FIELD1]&"' and [tblPARS_FIELD2] = '"&[frmInfo_test]!
[tblINFO_FIELD2]&"' and [tblPARS_FIELD3] = '"&[frmInfo_test]![tblINFO_FIELD3]
&"' and [tblPARS_FIELD4] = '"&[frmInfo_test]![tblINFO_FIELD4]&"'")

Can anyone help?

Thanks.
 
Thanks.

Unfortunately, the same error occurs: #Name?

This is the revised version:

=DlookUp("[tblPARS_FIELD5]", "tblPARS_TEST", "[tblPARS_FIELD1] = '" &
[frmInfo_test]![tblINFO_FIELD1] & "' and [tblPARS_FIELD2] = '" &
[frmInfo_test]![tblINFO_FIELD2] & "' and [tblPARS_FIELD3] = '" &
[frmInfo_test]![tblINFO_FIELD3] & "' and [tblPARS_FIELD4] = '" &
[frmInfo_test]![tblINFO_FIELD4] & "'")
 
You might try this:
With the form open (and your criteria filled in), open the code window,
then the immediate window (ctl-G if it's not displayed already).
In the immediate window, type in
?
followed by
"[tblPARS_FIELD1] = '"& [frmInfo_test]![tblINFO_FIELD1]&"' and
[tblPARS_FIELD2] = '"&[frmInfo_test]! [tblINFO_FIELD2]&"' and
[tblPARS_FIELD3] = '"&[frmInfo_test]![tblINFO_FIELD3] &"' and
[tblPARS_FIELD4] = '"&[frmInfo_test]![tblINFO_FIELD4]&"'"

See if it gives you what you expect...
 
Either add [Forms]! to the statements to fully qualify the references or
better yet use Me. basically, Access can't find the information that
you're lookin for.

[Forms]![frmInfo_test]![tblinfo_field1]
Me.tblInfo_Field1

I would point out though that tblInfo_Field1 is a bad name for a control
Thanks.

Unfortunately, the same error occurs: #Name?

This is the revised version:

=DlookUp("[tblPARS_FIELD5]", "tblPARS_TEST", "[tblPARS_FIELD1] = '" &
[frmInfo_test]![tblINFO_FIELD1] & "' and [tblPARS_FIELD2] = '" &
[frmInfo_test]![tblINFO_FIELD2] & "' and [tblPARS_FIELD3] = '" &
[frmInfo_test]![tblINFO_FIELD3] & "' and [tblPARS_FIELD4] = '" &
[frmInfo_test]![tblINFO_FIELD4] & "'")

You might try making sure there is a space before and after each ampersand.
Click to expand...
Click to expand...
 
Thanks.

I get: compile error: External name not found

What are the alternatives to dlookup? I want to identify a single value based
You might try this:
With the form open (and your criteria filled in), open the code window,
then the immediate window (ctl-G if it's not displayed already).
In the immediate window, type in
?
followed by
"[tblPARS_FIELD1] = '"&[frmInfo_test]![tblINFO_FIELD1]&"' and
[tblPARS_FIELD2] = '"&[frmInfo_test]! [tblINFO_FIELD2]&"' and
[tblPARS_FIELD3] = '"&[frmInfo_test]![tblINFO_FIELD3]&"' and
[tblPARS_FIELD4] = '"&[frmInfo_test]![tblINFO_FIELD4]&"'"

See if it gives you what you expect...
I'm trying to do a DLookup with four fields, all string values. The following
works when the tblINFO fields are assigned specific values, but gives an
Click to expand...
[quoted text clipped - 8 lines]
Thanks.
Click to expand...
Click to expand...
 
Is tblINFO_FIELD1 (and the others) controls on a form? If they are you
need to use the proper qualification to reference them which is

[Forms]![formName]![controlName]

or if the DLookup() is in a Form Module or Control

Me.controlName
Thanks.

I get: compile error: External name not found

What are the alternatives to dlookup? I want to identify a single value based
You might try this:
With the form open (and your criteria filled in), open the code window,
then the immediate window (ctl-G if it's not displayed already).
In the immediate window, type in
?
followed by
"[tblPARS_FIELD1] = '"&[frmInfo_test]![tblINFO_FIELD1]&"' and
[tblPARS_FIELD2] = '"&[frmInfo_test]! [tblINFO_FIELD2]&"' and
[tblPARS_FIELD3] = '"&[frmInfo_test]![tblINFO_FIELD3]&"' and
[tblPARS_FIELD4] = '"&[frmInfo_test]![tblINFO_FIELD4]&"'"

See if it gives you what you expect...

I'm trying to do a DLookup with four fields, all string values. The following
works when the tblINFO fields are assigned specific values, but gives an
Click to expand...

[quoted text clipped - 8 lines]
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Thanks.

The DLookup is in a form control.

Does this have the correct syntax:

=DlookUp("[tblPARS_FIELD5]", "tblPARS_TEST", "[tblPARS_FIELD1] = '" & Me.
tblINFO_FIELD1 & "' and [tblPARS_FIELD2] = '" & Me.tblINFO_FIELD2 & "' and
[tblPARS_FIELD3] = '" & Me.tblINFO_FIELD3 & "' and [tblPARS_FIELD4] = '" & Me.
tblINFO_FIELD4 & "'")

Still getting an error.


Is tblINFO_FIELD1 (and the others) controls on a form? If they are you
need to use the proper qualification to reference them which is

[Forms]![formName]![controlName]

or if the DLookup() is in a Form Module or Control

Me.controlName
Click to expand...
 
The DLookup is in a form control.

Does this have the correct syntax:

=DlookUp("[tblPARS_FIELD5]", "tblPARS_TEST", "[tblPARS_FIELD1] = '" & Me.
tblINFO_FIELD1 & "' and [tblPARS_FIELD2] = '" & Me.tblINFO_FIELD2 & "' and
[tblPARS_FIELD3] = '" & Me.tblINFO_FIELD3 & "' and [tblPARS_FIELD4] = '" & Me.
tblINFO_FIELD4 & "'")

Still getting an error.
Click to expand...

The syntax is correct if all four fields are of Text type, and if you
have form controls named the same as the table fields. That may be the
confusion. I'd suggest having the Textbox bound to tblPARS_FIELD1
named txttblPARS_FIELD1 for instance, so that Access (and you!)
doesn't get confused about whether you mean the form control or the
table field.

Also, the search will fail if any one of the four controls is NULL.

I didn't see the beginning of the discussion - what error message are
you getting?

John W. Vinson[MVP]
 
Thanks.

All four fields are text fields.

Regarding the field values, the two tables, tblPars_Test and tblInfo_Pars,
have corresponding fields containing identical values with the exception of
the fifth field of tblPars_Test, which contains the values being looked up.
The corresponding fields of tblInfo

There are no null values except for the fifth field of tblPars_Test.

The form and tables were set up specifically to test the dlookup.

The error message: #Name?

Does this have the correct syntax:

=DlookUp("[txttblPARS_FIELD5]", "tblPARS_TEST", "[txttblPARS_FIELD1] = '" &
Me.tblINFO_FIELD1 & "' and [txttblPARS_FIELD2] = '" & Me.tblINFO_FIELD2 & "'
and [txttblPARS_FIELD3] = '" & Me.tblINFO_FIELD3 & "' and [txttblPARS_FIELD4]
= '" & Me.tblINFO_FIELD4 & "'")

I still get a correct value if I replace the field names with actual values.


John said:
The DLookup is in a form control.
Click to expand...
[quoted text clipped - 6 lines]
Still getting an error.
Click to expand...

The syntax is correct if all four fields are of Text type, and if you
have form controls named the same as the table fields. That may be the
confusion. I'd suggest having the Textbox bound to tblPARS_FIELD1
named txttblPARS_FIELD1 for instance, so that Access (and you!)
doesn't get confused about whether you mean the form control or the
table field.

Also, the search will fail if any one of the four controls is NULL.

I didn't see the beginning of the discussion - what error message are
you getting?

John W. Vinson[MVP]
Click to expand...
 
Thanks.

All four fields are text fields.

Regarding the field values, the two tables, tblPars_Test and tblInfo_Pars,
have corresponding fields containing identical values with the exception of
the fifth field of tblPars_Test, which contains the values being looked up.
The corresponding fields of tblInfo
Click to expand...

If you want to see all of the values of Field5, you might want to
consider a Query joining tblPars_Test to tblInfo_Pars by the four
fields. No DLookUp needed!
There are no null values except for the fifth field of tblPars_Test.

The form and tables were set up specifically to test the dlookup.

The error message: #Name?
Click to expand...

That means that there is a name somewhere in your expression which
Access does not recognise.

Try changing all the Me. to Me! (though that shouldn't make a
difference) and doublecheck that there actually are form controls with
those values as their Name property, and table fields likewise.

John W. Vinson[MVP]
 
I've checked and double-checked the names, and can't find anything amiss.

I had a similar problem getting a VB module to work. The problem was solved
by adding a DAO Library. Could there be something similar missing, something
that needs to be installed to get the DLookup to work with the kind of
criteria I'm attempting to use?

John said:
Thanks.
Click to expand...
[quoted text clipped - 4 lines]
the fifth field of tblPars_Test, which contains the values being looked up.
The corresponding fields of tblInfo
Click to expand...

If you want to see all of the values of Field5, you might want to
consider a Query joining tblPars_Test to tblInfo_Pars by the four
fields. No DLookUp needed!
There are no null values except for the fifth field of tblPars_Test.

The form and tables were set up specifically to test the dlookup.

The error message: #Name?
Click to expand...

That means that there is a name somewhere in your expression which
Access does not recognise.

Try changing all the Me. to Me! (though that shouldn't make a
difference) and doublecheck that there actually are form controls with
those values as their Name property, and table fields likewise.

John W. Vinson[MVP]
Click to expand...
 
I've checked and double-checked the names, and can't find anything amiss.
Click to expand...

AHA!

The Me. (or Me!, either one) is valid within VBA code. However it
should be omitted in a Form or Report control's Control Source.

Remove all the Me and just use the control name in brackets.

John W. Vinson[MVP]
 
That worked!

Many thanks!!!



John said:
AHA!

The Me. (or Me!, either one) is valid within VBA code. However it
should be omitted in a Form or Report control's Control Source.

Remove all the Me and just use the control name in brackets.

John W. Vinson[MVP]
Click to expand...
 
Back
Top