Join not working 1

[atmospherik]

I have 5 tables:

tblProducts
tblSex
tblManufacturer
tblCondition
tblGarment
tblSize

tblProducts is the main table and it has Access dropdowns so that
tblProducts.Sex holds a number that relates to the foreign key tblSex.ID,
tblProducts.Manufacturer relates to the foreign key tblManufacturer.ID
and so on.

When I run the following query:

SELECT tblProducts.IDAuto,tblProducts.Remarks,tblProducts.Bought_Price,
tblProducts.Sell_Price,tblProducts.Sale_Price,tblProducts.Location,
tblProducts.Date_Sold,tblProducts.Txn_ID,tblProducts.Sold_To,
tblProducts.Date_Entered,tblProducts.Emailed_Out,tblProducts.Cotton,
tblGarment.Garment,tblSex.Sex,tblManufacturer.Manufacturer,tblSize.Size,
tblCondition.Condition

FROM tblProducts,tblGarment,tblSex,tblManufacturer,tblSize,tblCondition

WHERE tblProducts.Garment_Dropdown=tblGarment.ID

AND tblProducts.Sex_Dropdown=tblSex.ID
AND tblProducts.Manufacturer_Dropdown=tblManufacturer.ID
AND tblProducts.Size_Dropdown=tblSize.ID
AND tblProducts.Condition_Dropdown=tblCondition.ID

ORDER BY tblProducts.IDAuto

then the join works fine.

if I try to add another 'AND' clause to filter my results to say just "boys" ie....


SELECT tblProducts.IDAuto,tblProducts.Remarks,tblProducts.Bought_Price,
tblProducts.Sell_Price,tblProducts.Sale_Price,tblProducts.Location,
tblProducts.Date_Sold,tblProducts.Txn_ID,tblProducts.Sold_To,
tblProducts.Date_Entered,tblProducts.Emailed_Out,tblProducts.Cotton,
tblGarment.Garment,tblSex.Sex,tblManufacturer.Manufacturer,tblSize.Size,
tblCondition.Condition

FROM tblProducts,tblGarment,tblSex,tblManufacturer,tblSize,tblCondition

WHERE tblProducts.Garment_Dropdown=tblGarment.ID

AND tblProducts.Sex_Dropdown=tblSex.ID
AND tblProducts.Manufacturer_Dropdown=tblManufacturer.ID
AND tblProducts.Size_Dropdown=tblSize.ID
AND tblProducts.Condition_Dropdown=tblCondition.ID

AND tblSex.Sex=Boys

ORDER BY tblProducts.IDAuto

then I get the following error when I try to open the recordset..

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Can anybody spot my mistake - I've been trying to work out why for a couple of days now and it's really doing me in!

Many Thanks in advance,

Paul

 

[r937]

likely cause of problem: your table does not contain a column called Boys

likely solution: put Boys in quotes to make it a string value

i.e. AND tblSex.Sex='Boys'

r937.com | rudy.ca

 

[atmospherik]

Thanks for the quick answer r937 - that sorted it
(can't believe it was a quotes problem.. )

Thanks again,

Paul