DlookUp in a Query help

[Aaron]

I have two tables, Demoraphics1 and Demographics2. In Demographics1 I have a
lot of different data of which ID and Age are two sets. Demographics2
contains a subset of individuals from Demographics1. I am trying to setup a
query that will list each individual in Demographics2 and pull the Age from
Demographics1. Due to the way the table is constructed I cannot use the
relationship between identical IDs in Demographics1 and Demographics2. I am
trying to construct a Dlookup that will accomplish this for me, but I seem to
be running into a problem. When I use the following code:
F1DOB:
DLookUp("[Age]","Demographics1","[Demographics2]![ID]"=[Demographics1]![ID])
I get a message box asking for Demographics1!ID, where if I leave blank I
get the same value for all records and if I put a value in it I get nothing
for all records. Any help would be greatly appreciated.

 

[MGFoster]

I have two tables, Demoraphics1 and Demographics2. In Demographics1 I have a
lot of different data of which ID and Age are two sets. Demographics2
contains a subset of individuals from Demographics1. I am trying to setup a
query that will list each individual in Demographics2 and pull the Age from
Demographics1. Due to the way the table is constructed I cannot use the
relationship between identical IDs in Demographics1 and Demographics2. I am
trying to construct a Dlookup that will accomplish this for me, but I seem to
be running into a problem. When I use the following code:
F1DOB:
DLookUp("[Age]","Demographics1","[Demographics2]![ID]"=[Demographics1]![ID])
I get a message box asking for Demographics1!ID, where if I leave blank I
get the same value for all records and if I put a value in it I get nothing
for all records. Any help would be greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the syntax is incorrect and the concept is incorrect - you are
using a table reference that is not defined in the table expression
parameter.

Correct syntax would be:

DLookUp("[Age]","Demographics1","[ID]=" & [Demographics2].[ID])

If there was a Demographics2 defined in the table expression (2nd
parameter).

The way you used the criteria parameter indicates that you could JOIN
the two tables together on the ID:

SELECT D1.Age, <other columns>
FROM Demographics1 As D1 INNER JOIN Demographics2 As D2 ON D1.ID = D2.ID

Use a period between the table name and the column (field) name. The
exclamation point is used for MS Office collections (Forms!FormName
means the Form named "FormName" in the collection Forms).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdvbqIechKqOuFEgEQKjxQCff9Fu3N6d70PogBtcc1J7mZhW+rwAoLEW
STIlaBDq1afqYzql5wTlc6ag
=fJLL
-----END PGP SIGNATURE-----

 

[Aaron]

Thank you for your help.

I have changed my DLookUp function to match yours:
DLookUp("[Age]","Demographics1","[ID]=" & [Demographics2].[ID])

Now when I evaluate the query, I get a Enter Parameter Value box asking for
Demographics2.ID and when I click ok I get an error message stating “Syntax
error (missing operator) in query expression ‘[ID]=’â€

Thank you again for your help.

I have two tables, Demoraphics1 and Demographics2. In Demographics1 I have a
lot of different data of which ID and Age are two sets. Demographics2
contains a subset of individuals from Demographics1. I am trying to setup a
query that will list each individual in Demographics2 and pull the Age from
Demographics1. Due to the way the table is constructed I cannot use the
relationship between identical IDs in Demographics1 and Demographics2. I am
trying to construct a Dlookup that will accomplish this for me, but I seem to
be running into a problem. When I use the following code:
F1DOB:
DLookUp("[Age]","Demographics1","[Demographics2]![ID]"=[Demographics1]![ID])
I get a message box asking for Demographics1!ID, where if I leave blank I
get the same value for all records and if I put a value in it I get nothing
for all records. Any help would be greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the syntax is incorrect and the concept is incorrect - you are
using a table reference that is not defined in the table expression
parameter.

Correct syntax would be:

DLookUp("[Age]","Demographics1","[ID]=" & [Demographics2].[ID])

If there was a Demographics2 defined in the table expression (2nd
parameter).

The way you used the criteria parameter indicates that you could JOIN
the two tables together on the ID:

SELECT D1.Age, <other columns>
FROM Demographics1 As D1 INNER JOIN Demographics2 As D2 ON D1.ID = D2.ID

Use a period between the table name and the column (field) name. The
exclamation point is used for MS Office collections (Forms!FormName
means the Form named "FormName" in the collection Forms).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdvbqIechKqOuFEgEQKjxQCff9Fu3N6d70PogBtcc1J7mZhW+rwAoLEW
STIlaBDq1afqYzql5wTlc6ag
=fJLL
-----END PGP SIGNATURE-----

 

[Aaron]

Nevermind, I figured out the problem. I misunderstood one of the references
in your function. Thanks again for the help.

Thank you for your help.

I have changed my DLookUp function to match yours:
DLookUp("[Age]","Demographics1","[ID]=" & [Demographics2].[ID])

Now when I evaluate the query, I get a Enter Parameter Value box asking for
Demographics2.ID and when I click ok I get an error message stating “Syntax
error (missing operator) in query expression ‘[ID]=’â€

Thank you again for your help.

I have two tables, Demoraphics1 and Demographics2. In Demographics1 I have a
lot of different data of which ID and Age are two sets. Demographics2
contains a subset of individuals from Demographics1. I am trying to setup a
query that will list each individual in Demographics2 and pull the Age from
Demographics1. Due to the way the table is constructed I cannot use the
relationship between identical IDs in Demographics1 and Demographics2. I am
trying to construct a Dlookup that will accomplish this for me, but I seem to
be running into a problem. When I use the following code:
F1DOB:
DLookUp("[Age]","Demographics1","[Demographics2]![ID]"=[Demographics1]![ID])
I get a message box asking for Demographics1!ID, where if I leave blank I
get the same value for all records and if I put a value in it I get nothing
for all records. Any help would be greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, the syntax is incorrect and the concept is incorrect - you are
using a table reference that is not defined in the table expression
parameter.

Correct syntax would be:

DLookUp("[Age]","Demographics1","[ID]=" & [Demographics2].[ID])

If there was a Demographics2 defined in the table expression (2nd
parameter).

The way you used the criteria parameter indicates that you could JOIN
the two tables together on the ID:

SELECT D1.Age, <other columns>
FROM Demographics1 As D1 INNER JOIN Demographics2 As D2 ON D1.ID = D2.ID

Use a period between the table name and the column (field) name. The
exclamation point is used for MS Office collections (Forms!FormName
means the Form named "FormName" in the collection Forms).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdvbqIechKqOuFEgEQKjxQCff9Fu3N6d70PogBtcc1J7mZhW+rwAoLEW
STIlaBDq1afqYzql5wTlc6ag
=fJLL
-----END PGP SIGNATURE-----