DLookUp Criteria in a query

G

Guest

Hi, please help me with this problem. I have a table (Family Members)
containing ID, FullName and IDFamilyRelationship fields. For example, Mr. Ian
Roberts has the ID nº 2010001 and his wife, Mrs. Rosa Morales's ID is
2010002. Therefore, the IDFamilyRelationship of Mr. Ian is 2010002 and that
of Mrs. Rosa is 2010001. Now, I want to create a QUEYRY based on this table
showing its 3 fields and, ADDITIONALLY, a new field named SPOUSE, using the
DLookUp Function, with the following syntax:
DLookUp("[FullName]";"Family Members";"[IDFamilyRelationship]=[ID]"), in
order to show the spouse's name in the same record row of the person in
question (Eg.: In the row concerning Mr. Ian Roberts, the column SPOUSE
should indicate the name Rosa Morales, but there is now result. This DLookUp
function works well if the criteria is fixed (eg. ID=2010001), or if this
criteria is set in a form or report (where we can use the control name), but
I can't find the way to give a criteria that is not fixed in a QUERY. So,
please help. I would very much appreciate it.
 
L

Larry Daugherty

Try
DLookUp("[FullName]";"Family Members";"[IDFamilyRelationship]=" &
[Me.IDFamilyRelationship]),

HTH
--
-Larry-
--

LPC said:
Hi, please help me with this problem. I have a table (Family Members)
containing ID, FullName and IDFamilyRelationship fields. For example, Mr. Ian
Roberts has the ID nº 2010001 and his wife, Mrs. Rosa Morales's ID is
2010002. Therefore, the IDFamilyRelationship of Mr. Ian is 2010002 and that
of Mrs. Rosa is 2010001. Now, I want to create a QUEYRY based on this table
showing its 3 fields and, ADDITIONALLY, a new field named SPOUSE, using the
DLookUp Function, with the following syntax:
DLookUp("[FullName]";"Family
Members";"[IDFamilyRelationship]=[ID]"), in
 
G

Guest

Hi, many thanks for the suggestion. I tried it but unfornately, I didn't
work. Access gave the following message: Syntax error (missing operator) in
query expression '[IDFamilyRelationship]='
Thanks again and in advance for other suggestions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top