 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005DLookup is incredibly aggravating
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. > For some reason I have the hardest time with DLookup syntax. Please > help! [quoted text clipped - 11 lines] > 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.  SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com 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! [quoted text clipped - 14 lines] > 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 expect MEMBER_ID is text. If so, try set the third arguement to: "[MEMBER_ID]=""" & [Primary_Contact] & """" SignatureDuane 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 [quoted text clipped - 20 lines] >> quotes. The >> inside quotes for the space should be single instead as well. 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] & """" [quoted text clipped - 23 lines] > >> quotes. The > >> inside quotes for the space should be single instead as well. I expect you have some records with a Primary_Contact of NULL. Try this "[MEMBER_ID]=" & Nz([Primary_Contact],-1 SignatureDuane Hookom MS Access MVP -- > Nope. It's a number. It's my autonumber key field for the tblMembership, > and [quoted text clipped - 34 lines] >> >> quotes. The >> >> inside quotes for the space should be single instead as well. 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 [quoted text clipped - 37 lines] > >> >> 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 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. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com This should work: TestFld: DLookup("FieldName & ' ' " , "TableName") SignatureKen Snell <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 [quoted text clipped - 11 lines] > So apparently the single quotes are ok as long as you are not doing any > concatenation. This will work PriContactName: DLookUp("[FIRST_NAME] & Chr(32) & [LAST_NAME]","[tblMembership]","[MEMBER_ID]=" & [Primary_Contact]) > For some reason I have the hardest time with DLookup syntax. Please help! > [quoted text clipped - 9 lines] > For the life of me I don't know why it's giving me this grief. > thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.