Search Query query :)

[XelaIrodavlas]

I have a query I'm using in a search form to look up employees based on their ID num, title or first or second name. But for some reason it's only showing me complete records, so if for example the person didn't put in their second name, they don't show up at all.

This is what I have put in the criteria section of the Query beneath each of the relevant fields:

Like "*" & [Forms]![SearchForm]![TxtBoxName] & "*"


Does anyone know how I can fix it so that if for example I search only for people with first name 'alex' it will show all records regardless of whether the other three fields have been filled. Thank you!

 

[pr2-eugin]

Is that not the main point of Search.. It should have met some criteria?? Based on how you have it.. It will be like..

[ID] AND [firstName] AND [lastName] AND [title]

But you wish to have is.. something like..

[ID] AND ([TITLE] OR [firstName] OR [lastName])

But if you want to search irregardless of what is entered in either of the boxes.. then try this..

In my opinion this is not great.. as it will not use the indexing, nor it will be a great result set..

 

[XelaIrodavlas]

You make a good point Paul, I will have to reconsider. But thanks for the explanation (as usual!)

 

[XelaIrodavlas]

Still no luck despite hours trolling the internet... I still want the query to return all the data (name AND Details, not OR) But I can't allow it to ignore entire records just because one (often irrelevant) piece of information is missing...

Lots of people online have asked if there's a way to get the search query to ignore null fields in a table, or to act as if they are "none" or "n/a" etc. but no-one seems to have an answer. Is this simply not possible? or does it involve a level of code beyond most of us mortals?

There must be a solution... or should I just enter n/a into all the blank fields in the data tables? and is there a way to do that automatically?

I pray to the gods of Access

 

[pr2-eugin]

Show the query you have so far.. also give some sample data and the result you wish to see..

 

[XelaIrodavlas]

you asked for it here's the SQL:

SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
WHERE ((([TBL Employee Details].EmpDetID) Like "*" & [Forms]![iFRM 1st basic]![ID] & "*") AND (([TBL Employee Details].[First Name(s)]) Like "*" & [Forms]![iFRM 1st basic]![First Name] & "*") AND (([TBL Employee Details].Surname) Like "*" & [Forms]![iFRM 1st basic]![Surname] & "*") AND (([TBL Employee Details].Postcode) Like "*" & [Forms]![iFRM 1st basic]![Postcode] & "*") AND (([TBL Training Qualifications].QuaName) Like "*" & [Forms]![iFRM 1st basic]![Qual] & "*"));

I've attached what that looks like..

I can't include any sample data, but its pretty regular stuff, Name, Title, Address, Postcode and qualifications. But this is not 100% complete for all employees, for example a lot of them are missing titles, and many have no qualifications on record.

I would like to see a list of all employees who match the entered fields (ID, first name, surname, postcode or Quali) including those who don't have 100% complete records.

 

[MSAccessRookie]

Have you tried a UNION Query? It will get all records for each of the criteria, and in the event one of the Sub-Queries is missing a record, another of them will most likely contain the record. In addition, a standard UNION Query will eliminate duplicates, so the ending recordset could be all of the records that you are looking for.

Select { What you are looking for } From tblEmployeeRecords
Where ID Like "*" & [Forms]![SearchForm]![[COLOR=blue]IDControlName[/COLOR]] & "*"
UNION Select { What you are looking for } From tblEmployeeRecords
Where firstName Like "*" & [Forms]![SearchForm]![[COLOR=blue]firstnameControlName[/COLOR]] & "*"
UNION Select { What you are looking for } From tblEmployeeRecords
Where lastName Like "*" & [Forms]![SearchForm]![[COLOR=blue]lastNameControlName[/COLOR]] & "*"
UNION Select { What you are looking for } From tblEmployeeRecords
Where title Like "*" & [Forms]![SearchForm]![[COLOR=blue]titleControlName[/COLOR]] & "*"

 

[pr2-eugin]

Yes, MSAccessRookie's suggestion seems to be more interesting.. I was about to make a stupid suggestion of adding a Is Null criteria with every condition.. that way it will return even if one of the field isempty .. something like..

 

[MSAccessRookie]

Yes, MSAccessRookie's suggestion seems to be more interesting.. I was about to make a stupid suggestion of adding a Is Null criteria with every condition.. that way it will return even if one of the field isempty .. something like..

I suppose that you could do that, but it should not ne necessary, unless you also want records where NONE of the criteria is present. Using the Union should get all of the records you asked for.

 

[XelaIrodavlas]

Sounds promising, unfortunately I'm too rubbish to apply it correctly. Any chance I could ask you to take a look at my code and see where I've gone wrong??? I keep getting an error message for syntax in the FROM line...

SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
Where ID Like "*" & [Forms]![iFRM 1st basic]![ID] & "*"
UNION Select [EmpDetID] From TBL Employee Details
Where firstName Like "*" & [Forms]![iFRM 1st basic]![First Name] & "*"
UNION Select [First Name(s)] From TBL Employee Details
Where lastName Like "*" & [Forms]![iFRM 1st basic]![Surname] & "*"
UNION Select [Surname] From TBL Employee Details
Where title Like "*" & [Forms]![iFRM 1st basic]![title] & "*"

already see one problem... Corrected but still syntax error in FROM

 

[pr2-eugin]

Alex, I think it should be something like this..

SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
Where ID Like "*" & [Forms]![iFRM 1st basic]![ID] & "*"
UNION SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
Where firstName Like "*" & [Forms]![iFRM 1st basic]![First Name] & "*"
UNION SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
Where lastName Like "*" & [Forms]![iFRM 1st basic]![Surname] & "*"
UNION SELECT [TBL Employee Details].EmpDetID, [TBL Employee Details].Title, [TBL Employee Details].[First Name(s)], [TBL Employee Details].Surname, [TBL Employee Details].Postcode, [TBL Employee Details].[Home Telephone], [TBL Training Qualifications].QuaName, [rTBL Employees-Training].[E-T FKTrainingID]
FROM [TBL Training Qualifications] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
Where title Like "*" & [Forms]![iFRM 1st basic]![title] & "*"

 

[XelaIrodavlas]

Hmm no luck. But you've definitely put me on the right track, i'm reading up on union queries as we speak

Thanks for all the help guys

 

[XelaIrodavlas]

I think (and do correct me if I'm wrong) the problem isn't that there are holes in the employee details table, it seems to pick up on those as long as i've searched for at least one relevant field. The problem is when that employee has no involvement with one of the other tables. For example TrainingQualifications... it's only the employees who have no recorded training quals who aren't turning up showing up at all... weird...

 

[XelaIrodavlas]

Finally solved this issue so for the sake of anyone else reading I will explain:

1) Of course using the correct joins is the way to get all employees regardless of whether they have an entry in other tables - my issue was that some of the other tables were already related (thanks to the relationships view in acc 2010). The SQL didn't know which joins to prioritise. So for the sake of this query I deleted the excess joins as they weren't necessary here.

2) Then it's just about using the right statement in the criteria, you can include all the Like "*" stuff if you want to, but essentially all you need is, criteria = [forms]![myform]![mycontrol] OR [forms]![myform]![mycontrol] is null (essentially what PR2-Eugin said, so extra thanks!). This then allows you to enter text into any number of the form controls, and will include all records where fields are blank.

3) This does create an unusual problem within the query itself - for some reason when I close it and open it again it creates a very large amount of Or criteria (I suspect one for each possible combination of controls) which crashes the system if you try to view in sql mode. however I found that after repeating steps 1 and 2 - without closing the query, I could go to SQL view, copy the code from there and then copy it directly into the source of whatever form or record i need - without any weirdness going on.

I Hope this is useful to at least someone, as I know what a ball ache this was for me.

Thanks all,