Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Nested Select Query Error!! URGENT!!

Author  Topic  dpac Starting Member 2 Posts Posted - 2005-11-04 : 18:28:08 Alrite! I have 4 Tables namely tblCaseInventory, tblDiscMailOneBatches, tblDiscMailTwoBatches, tblDiscMailThreeBatches. The 3 Batches tables are linked to the CaseInventory table thro SEPERATE fields. These fields may b empty for certain records which is irrelevant. I've created a search form to display the search results from these tables. My problem is, the query i've written pulls up the records from the CaseInventory table only where all the fields are filled, not the records where the fields are blank. SELECT tblCaseInventory.fldCaseNumber, tblCaseInventory.fldBusinessName, tblCaseInventory.fldSiteCity, tblCaseInventory.fldSiteZipCode, tblCaseInventory.fldPhoneNumber, tblCaseInventory.fldBusinessLicNumber, tblCaseInventory.fldSiteAddressOne FROM tblDiscMailTwoBatches RIGHT JOIN (tblDiscMailThreeBatches RIGHT JOIN (tblDiscMailOneBatches RIGHT JOIN tblCaseInventory ON tblDiscMailOneBatches.fldDMail1BatchID = tblCaseInventory.fldDiscMail_1BatchID) ON tblDiscMailThreeBatches.fldDMail3BatchID = tblCaseInventory.fldDiscMail_3BatchID) ON tblDiscMailTwoBatches.fldDMail2BatchID = tblCaseInventory.fldDiscMail_2BatchID WHERE (((tblCaseInventory.fldBusinessName) Like "*" & [Forms].[Form1].[Text5] & "*") AND ((tblCaseInventory.fldSiteCity) Like "*" & [Forms].[Form1].[City] & "*") AND ((tblCaseInventory.fldSiteZipCode) Like "*" & [Forms].[Form1].[Text15] & "*") AND ((tblCaseInventory.fldPhoneNumber) Like "*" & [Forms].[Form1].[Text10] & "*") AND ((tblCaseInventory.fldBusinessLicNumber) Like "*" & [Forms].[Form1].[Text2] & "*") AND ((tblCaseInventory.fldSiteAddressOne) Like "*" & [Forms].[Form1].[Text8] & "*")); I tried this QUERY so tht it pulls up every record in the main table and every record in the related table "if" it contains a value in that field. Apparently the error displayed says "SYNTAX ERROR". PLZZ help me out SELECT tblCaseInventory.fldCaseNumber, tblCaseInventory.fldBusinessName, tblCaseInventory.fldSiteCity, tblCaseInventory.fldSiteZipCode, tblCaseInventory.fldPhoneNumber, tblCaseInventory.fldBusinessLicNumber, tblCaseInventory.fldSiteAddressOne FROM tblCaseInventory where tblCaseInventory.fldDiscMail_1BatchID IN (Select * from tblDiscMailOneBatches, tblCaseInventory where (tblDiscMailOneBatches.fldDMail1BatchID = tblCaseInventory.fldDiscMail_1BatchID)) AND tblCaseInventory.fldDiscMail_3BatchID IN (Select * from tblDiscMailThreeBatches, tblCaseInventory where (tblDiscMailThreeBatches.fldDMail3BatchID = tblCaseInventory.fldDiscMail_3BatchID)) AND tblCaseInventory.fldDiscMail_2BatchID IN (Select * from tblDiscMailTwoBatches, tblCaseInventory Where (tblDiscMailTwoBatches.fldDMail2BatchID = tblCaseInventory.fldDiscMail_2BatchID)) WHERE (((tblCaseInventory.fldBusinessName) Like "*" & [Forms].[Form1].[Text5] & "*") AND ((tblCaseInventory.fldSiteCity) Like "*" & [Forms].[Form1].[City] & "*") AND ((tblCaseInventory.fldSiteZipCode) Like "*" & [Forms].[Form1].[Text15] & "*") AND ((tblCaseInventory.fldPhoneNumber) Like "*" & [Forms].[Form1].[Text10] & "*") AND ((tblCaseInventory.fldBusinessLicNumber) Like "*" & [Forms].[Form1].[Text2] & "*") AND ((tblCaseInventory.fldSiteAddressOne) Like "*" & [Forms].[Form1].[Text8] & "*"));