DLookup is incredibly aggravating

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

Guest

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.
 
BrianS said:
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.
Click to expand...

You have single quotes around the first argument instead of double quotes. The
inside quotes for the space should be single instead as well.
 
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



Rick Brandt said:
BrianS said:
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.
Click to expand...

You have single quotes around the first argument instead of double quotes. The
inside quotes for the space should be single instead as well.
Click to expand...
 
I expect MEMBER_ID is text. If so, try set the third arguement to:
"[MEMBER_ID]=""" & [Primary_Contact] & """"


--
Duane Hookom
MS Access MVP
--

BrianS said:
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



Rick Brandt said:
BrianS said:
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.
Click to expand...

You have single quotes around the first argument instead of double
quotes. The
inside quotes for the space should be single instead as well.
Click to expand...
Click to expand...
 
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


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


--
Duane Hookom
MS Access MVP
--

BrianS said:
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



Rick Brandt said:
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.
Click to expand...
Click to expand...
Click to expand...
 
BrianS said:
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
Click to expand...

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

BrianS said:
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


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


--
Duane Hookom
MS Access MVP
--

BrianS said:
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.
Click to expand...
Click to expand...
Click to expand...
 
This will work
PriContactName: DLookUp("[FIRST_NAME] & Chr(32) &
[LAST_NAME]","[tblMembership]","[MEMBER_ID]=" & [Primary_Contact])
 
That's it!
Thanks -- I completely overlooked the fact that a null value in
Primary_Contact would screw things up.
thanks!
Brian

Duane Hookom said:
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
--

BrianS said:
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


Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top