Query on table with a lookup

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I created a table with a lookup field to another table.
I've got a module in which I am using a SQL statement
based on the table with the lookup. For example:

sSQL = "Select * From CorporateExtVMX where Dept = '" &
Dpt & "'"

Dept is the field that is the lookup.

When running this code, I get the error message that a
parameter is blank. So I took this bit of code and
created a Query with it. I got a popup box to fill in
Dept.

Now, using the the Query wizard, I was able to create a
Query and the where clause in the SQL ended up
being "where Departmet = 'ACCOUNTING'" Note that this
time Department is spelled out. Department is the field
that is being looked up in the Dept table. But in my
CorporateVMXExt table, the field is Dept.

I apologize if I'm confusing anyone. :)

Okay, so I copied the Query SQL and posted it in the
Module SQL, so now it is this:
sSQL = "Select * From CorporateExtVMX where Department
= '" & Dpt & "'"

And after that I was getting the error message that
Department is not a field in the table. And it's not!
It's a field in the lookup table.

I guess my question is, how am I supposed to write a SQL
query on a table in which one field is looking up a table
from another table? This is driving me nuts!

Thanks,
Jennifer
 
For the SQL statemen

Select * From CorporateExtVMX where Dept = 'ABC

to work, the table CorporateExtVMX must have a field named Dept. I don't think your table has this field because, when MS-Access finds a non-field in the WHERE clause, it pops-up a window asking for your input. You have written that you have a table named Dept with the field Department in it. I assume that it also has a field like DeptCode. If the Dept table has this field with a department code for each department and the table CorporateExtVMX has a field named DeptCode with the appropriate code for each record, then you can join the two tables for a look-up as follows

Select * From CorporateExtVM
INNER JOIN Dept ON CorporateExtVMX.DeptCode = Dept.DeptCod

Here, the DeptCode field is the link between the two tables


----- Jen wrote: ----

I created a table with a lookup field to another table.
I've got a module in which I am using a SQL statement
based on the table with the lookup. For example

sSQL = "Select * From CorporateExtVMX where Dept = '" &
Dpt & "'

Dept is the field that is the lookup.

When running this code, I get the error message that a
parameter is blank. So I took this bit of code and
created a Query with it. I got a popup box to fill in
Dept

Now, using the the Query wizard, I was able to create a
Query and the where clause in the SQL ended up
being "where Departmet = 'ACCOUNTING'" Note that this
time Department is spelled out. Department is the field
that is being looked up in the Dept table. But in my
CorporateVMXExt table, the field is Dept

I apologize if I'm confusing anyone. :

Okay, so I copied the Query SQL and posted it in the
Module SQL, so now it is this
sSQL = "Select * From CorporateExtVMX where Department
= '" & Dpt & "'

And after that I was getting the error message that
Department is not a field in the table. And it's not!
It's a field in the lookup table

I guess my question is, how am I supposed to write a SQL
query on a table in which one field is looking up a table
from another table? This is driving me nuts

Thanks
Jennifer
 
Back
Top