DLookup is incredibly aggravating

[BrianS]

For some reason I have the hardest time with DLookup syntax. Please help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID (in
tblMembership) = Primary_Contact in tbl Business. Here's my DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

 

[Rick Brandt]

For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double quotes. The
inside quotes for the space should be single instead as well.

 

[BrianS]

I still get the same error message. I think the single/double can be used
interchangeably here. The problem is with the criteria in the third argument.
-Brian

For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double quotes. The
inside quotes for the space should be single instead as well.

 

[Duane Hookom]

I expect MEMBER_ID is text. If so, try set the third arguement to:
"[MEMBER_ID]=""" & [Primary_Contact] & """"


--
Duane Hookom
MS Access MVP
--

I still get the same error message. I think the single/double can be used
interchangeably here. The problem is with the criteria in the third
argument.
-Brian

For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double
quotes. The
inside quotes for the space should be single instead as well.

 

[BrianS]

Nope. It's a number. It's my autonumber key field for the tblMembership, and
thus the basis for my table linking.

I tried what you provided anyway, just for kicks, and got the type mismatch
error message.

-Brian

I expect MEMBER_ID is text. If so, try set the third arguement to:
"[MEMBER_ID]=""" & [Primary_Contact] & """"


--
Duane Hookom
MS Access MVP
--

I still get the same error message. I think the single/double can be used
interchangeably here. The problem is with the criteria in the third
argument.
-Brian

BrianS wrote:
For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double
quotes. The
inside quotes for the space should be single instead as well.

 

[Rick Brandt]

I still get the same error message. I think the single/double can be
used interchangeably here. The problem is with the criteria in the
third argument. -Brian

In my test this worked...
TestFld: DLookup('FieldName', "TableName")

This did not...
TestFld: DLookup('FieldName' & " " , "TableName")

Nor did this...
TestFld: DLookup('FieldName' & ' ' , "TableName")

So apparently the single quotes are ok as long as you are not doing any
concatenation.

 

[Duane Hookom]

I expect you have some records with a Primary_Contact of NULL. Try this
"[MEMBER_ID]=" & Nz([Primary_Contact],-1

--
Duane Hookom
MS Access MVP
--

Nope. It's a number. It's my autonumber key field for the tblMembership,
and
thus the basis for my table linking.

I tried what you provided anyway, just for kicks, and got the type
mismatch
error message.

-Brian

I expect MEMBER_ID is text. If so, try set the third arguement to:
"[MEMBER_ID]=""" & [Primary_Contact] & """"


--
Duane Hookom
MS Access MVP
--

I still get the same error message. I think the single/double can be
used
interchangeably here. The problem is with the criteria in the third
argument.
-Brian



:

BrianS wrote:
For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double
quotes. The
inside quotes for the space should be single instead as well.

 

[Dennis]

This will work
PriContactName: DLookUp("[FIRST_NAME] & Chr(32) &
[LAST_NAME]","[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

 

[BrianS]

That's it!
Thanks -- I completely overlooked the fact that a null value in
Primary_Contact would screw things up.
thanks!
Brian

I expect you have some records with a Primary_Contact of NULL. Try this
"[MEMBER_ID]=" & Nz([Primary_Contact],-1

--
Duane Hookom
MS Access MVP
--

Nope. It's a number. It's my autonumber key field for the tblMembership,
and
thus the basis for my table linking.

I tried what you provided anyway, just for kicks, and got the type
mismatch
error message.

-Brian

I expect MEMBER_ID is text. If so, try set the third arguement to:
"[MEMBER_ID]=""" & [Primary_Contact] & """"


--
Duane Hookom
MS Access MVP
--

I still get the same error message. I think the single/double can be
used
interchangeably here. The problem is with the criteria in the third
argument.
-Brian



:

BrianS wrote:
For some reason I have the hardest time with DLookup syntax. Please
help!

I'm trying to lookup a contact name in tblMembership where MEMBER_ID
(in tblMembership) = Primary_Contact in tbl Business. Here's my
DLookup:

PriContactName: DLookUp('[FIRST_NAME] & " " &
[LAST_NAME]',"[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])

I'm getting the following error message:
Syntax error (missing operator) in query expression '[MEMBER_ID]='.

For the life of me I don't know why it's giving me this grief.
thanks.

You have single quotes around the first argument instead of double
quotes. The
inside quotes for the space should be single instead as well.

 

[Ken Snell [MVP]]

This should work:

TestFld: DLookup("FieldName & ' ' " , "TableName")