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.

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

HTH
--
-Larry-
--

"LPC" <(E-Mail Removed)> wrote in message
news:2C86666B-9061-48F3-9D3F-(E-Mail Removed)...
> 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.

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
"LPC" wrote:

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