Trouble creating Query

[Guest]

I have an invoice table showing (among others) "vendor name" and "phase".
I also have a vendors table including "vendor name" and "vendor number". In
a third table, I have "phase" and "phase codes". I want to end up with a
query (and subsequently a report) pulling in the "vendor number"
corresponding to the "vendor name" and the "phase code" corresponding to the
"phase". With no criteria added, I would think it should show all invoices in
the table with the corresponding added 2 columns from the other 2 tables.
Instead, I get "type mismatch in expression". When I look at the
relationships, it has connected the invoices table items to the
Access-assigned ID(primary key) in each of the other 2 tables. If I change
the relationships to what I think it should be, it still gives me "type
mismatch in expression". What am I not understanding about this? Thanks for
any help you can give me.

 

[strive4peace]

Hi Rich,

make sure that the DATA TYPE of the fields you are linking on is the
same in each table.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Yes, they are different. The field in the Invoice table is a drop down list
box, so that comes up as a number type as soon as I created it. The Vendors
file has the Vendor Name simply as a text file. I don't see how they can not
be different if I want the one to be a list box and the other to be justa
table of the names.

 

[strive4peace]

Hi Rich,

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

DATA TYPE is not the same thing as DISPLAY CONTROL

The proper place for comboxes and listboxes is on forms -- you should
not enter data directly into tables anyway

The Data Type for fields you use to link needs to be the same. If you
are linking to an Autonumber field, that is a Long Integer data type


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*