DLOOKUP error AGAIN!!!

Guest · May 30, 2004

Hi there

I've to thank people here in this discussion group for helping me out. However, now I've run into another problem and I would really appreciate if somebody coule help me out

In my table "tblData", I've the following data

Employee_I
Last_Nam
First_Nam
Departmen
Office_Cit

Now, I've made a query that searches for all the employees in a particular city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan, Berlin, New York, LA etc....

This query is working very well for a particular city like NY, Paris etc.. However, now I want to run a query for all the employees (First_Name, Last_Name) working in Euro-zone, Europe, Americas, and globally. For Euro-zone, I want Paris, Brussels, Milan and Berlin; for Europe I want all the european cities and for America, NY and LA.

For this I created another table "tblCity" with the following format
Field Names: Global, Europe, Eurozone, America
After which the column contains the city names; e.g.

Eurozone (Field Name
Pari
Mila
Berli
Brussel
....

After doing this I made a query "qryCity" which displays the name of the American cities where the office is based (NY and LA)

Now this query is based on the table "tblCity" and returns the names of the two cities (NY and LA). I then made another query "qrySearch" based on the previous query "qryCity" and linking it with the original table "tbData"
=DLookUp("[Americas]","qryCity"

Thus this query takes the city names from the result of the query "qryCity" and then searches the employees names in the basic table "tblData"

This works really well, HOWEVER, it finds out the records of only the NY office!!!! and NOT the LA office!!

Actually, the result of "qryCity" i
N
L

And the query based on this (since it uses the DLOOKUP function) only finds the data for NY and not LA!!!

I want the second query to give me the employees list for BOTH NY and LA!! Can anybody PLEASE help me out here??

I shall be very grateful if somebody could help me out here

Regards
Mike

MacDermott · May 30, 2004

If you'll read the description of the DLookup() function in the Access Help
file, you'll see that it returns a single value - the first item in the
table/query which matches the criteria.

So, quite aside from the fact that it's rarely advisible to use aggregate
functions inside a query, in this case DLookup is not even suitable for your
purpose.

I'd suggest using a query with an inner join between qryCity and tblData.

HTH
- Turtle

Scarty said:
Hi there,

I've to thank people here in this discussion group for helping me out.

However, now I've run into another problem and I would really appreciate if
somebody coule help me out.

In my table "tblData", I've the following data;

Employee_ID
Last_Name
First_Name
Department
Office_City

Now, I've made a query that searches for all the employees in a particular

city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan,
Berlin, New York, LA etc.....

This query is working very well for a particular city like NY, Paris etc..

However, now I want to run a query for all the employees (First_Name,
Last_Name) working in Euro-zone, Europe, Americas, and globally. For
Euro-zone, I want Paris, Brussels, Milan and Berlin; for Europe I want all
the european cities and for America, NY and LA.

For this I created another table "tblCity" with the following format:
Field Names: Global, Europe, Eurozone, Americas
After which the column contains the city names; e.g.,

Eurozone (Field Name)
Paris
Milan
Berlin
Brussels
....

After doing this I made a query "qryCity" which displays the name of the

American cities where the office is based (NY and LA).

Now this query is based on the table "tblCity" and returns the names of

the two cities (NY and LA). I then made another query "qrySearch" based on
the previous query "qryCity" and linking it with the original table
"tbData".

=DLookUp("[Americas]","qryCity")

Thus this query takes the city names from the result of the query

"qryCity" and then searches the employees names in the basic table
"tblData".

This works really well, HOWEVER, it finds out the records of only the NY

office!!!! and NOT the LA office!!

Actually, the result of "qryCity" is
NY
LA

And the query based on this (since it uses the DLOOKUP function) only

finds the data for NY and not LA!!!

I want the second query to give me the employees list for BOTH NY and LA!!

Can anybody PLEASE help me out here???

Mekacher Antonella · May 31, 2004

plis language of italien