Hi,
Feel like I am losing my mind a bit with this one.
frmSearchF is a form used to perform a search that pulls data from various tables via a query, SearchQ.
I can use frmSearchF to search by Company, by Service, by Number of Issues - any combination of these (filled in or left blank) works perfectly.
My problem is that now if I add a new company, the search returns zero results, and I have no idea why. The same issue does NOT occur if I add a new Service to and existing company.
This is the SQL of my Query:
SELECT tblCompanies.Company, tblServicePrice.Price, tblServicePrice.Currency, tblServicePrice.[Number of Issues], tblServicePrice.Comments, tblDeadlines.Deadline, tblDeadlines.Issue, tblServicePrice.Service
FROM (tblCompanies INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company) INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company
WHERE (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company])) OR (((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Number of Issues]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Service]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.[Number of Issues])=[Forms]![SearchF]![Number of Issues]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Service]) Is Null)) OR (((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Number of Issues]) Is Null) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null));
I don't understand why it won't find any new companies that I add. Those newly added companies get put into the same table (tblCompanies.Company) as the other companies. Additionally, my form SearchF utilises combo boxes for Company and Service fields, which auto-populate based on the companies listed in tblCompanies, and the new companies DO show up in this drop down box.
Please help!