DLookup help

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.

You might try making sure there is a space before and after each ampersand.

"Stoneface" u16334@uwe wrote in message news:583f6fb824853@uwe...
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] & "'")


MacDermott wrote:
You might try making sure there is a space before and after each ampersand.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200512/1

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...

"Stoneface" u16334@uwe wrote in message news:583f6fb824853@uwe...
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.

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
on a form since other persons might take it that you're trying to
reference a field in a table (as opposed to a control).

Stoneface via AccessMonster.com wrote:
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] & "'")


MacDermott wrote:

You might try making sure there is a space before and after each ampersand.

Thanks.

I get: compile error: External name not found

What are the alternatives to dlookup? I want to identify a single value based
on four criteria, as in my dlookup. The value will be used in a calculation.


MacDermott wrote:
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
[quoted text clipped - 8 lines]

Thanks.

--
Message posted via http://www.accessmonster.com

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

Stoneface via AccessMonster.com wrote:
Thanks.

I get: compile error: External name not found

What are the alternatives to dlookup? I want to identify a single value based
on four criteria, as in my dlookup. The value will be used in a calculation.


MacDermott wrote:

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

[quoted text clipped - 8 lines]

Thanks.

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.



David C. Holley wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200512/1

On Wed, 07 Dec 2005 04:16:02 GMT, "Stoneface via AccessMonster.com"
u16334@uwe wrote:

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.

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 Vinson wrote:
The DLookup is in a form control.

[quoted text clipped - 6 lines]

Still getting an error.

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]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200512/1