By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Help with search function

P: 15
hi.. i am currently creating an employee database.. and there is a form on
the database that needs to search on records.. i am planning to create a form
with a single textbox (for the keyword entry) and a search command button
that when clicked, the search results will be shown in a seaparate continuous
form.

now here is my query:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName,
  2. tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
  3. tblOutlet.OutletName, tblPosition.PositionName
  4. FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN
  5. tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.
  6. DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.
  7. OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee.
  8. PositionIDFK
  9. WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor])
  10. ) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR
  11. (((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
  12. (tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
  13. (tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
  14. (tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
  15. (tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
  16. (tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor])); 
but when i click search, the results are empty. what could be wrong with the
query? is it the query? what should be the record source for the search form
and the result form?

if you want to see the actual ms access file, here it is:
http://www.gigafiles.co.uk/files/636/human%20resource%20info%20system_2006-10-11.zip

the name of the form is frmSearchEmployee and frmSearchResultEmployee and the
name of the query is qrySearchResultEmployee..

thanks a lot and God bless..
Oct 17 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Does this part of the query work and return all records?

SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
tblOutlet.OutletName, tblPosition.PositionName
FROM (tblOutlet INNER JOIN
(tblDealer INNER JOIN (tblCompany INNER JOIN tblPosition
ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK)
ON tblDealer.DealerIDPK = tblPosition.DealerIDFK)
ON tblOutlet.OutletIDPK = tblPosition.OutletIDFK)
INNER JOIN tblEmployee
ON tblPosition.PositionIDPK = tblEmployee.PositionIDFK;
Oct 17 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.