Optimize a select query

[accessnewb]

Hi,
I have a search button and when the button is clicked, a query is executed. This is how the query looks like:



SELECT *
FROM Contacts AS t
WHERE (t.ContactName Like IIf(IsNull(FORMS!F_Products_Search!Text34),"*",(FO RMS!F_Products_Search!Text34)) Or (IIf(IsNull(FORMS!F_Products_Search!Text34),t.Prod uctName is null))) And
(t.FPItem Like IIf(IsNull(FORMS!F_Products_Search!Text48),"*",(FO RMS!F_Products_Search!Text48)) Or (IIf(IsNull(FORMS!F_Products_Search!Text48),t.FPIt em is null)))...


The problem with this query is that, it doesn't search for partial names. As an example, if I need to search for "Mathew", but I type it only "Mat", nothing is retreived. I need to explicitly state Mat* to search for partial names.

Also, even if i specify "thew" and hit search, it should bring up mathew.

Any suggestions???

[June7]

This tutorial shows how to set up a search form and how to set the parameters of a query based on the form. http://www.datapigtechnologies.com/f...earchform.html

[accessnewb]

Hi,
Thank you for the tutorial. It worked! I don't have to explicitly type * anymore. But there are still some issues with it .

All the fields need to have some data in it for the search to retrieve a particular column. For example, I have a field called "Middle name" and if the entry is blank then that column is not retrieved in the search.

Also, the query in the tutorial does not search for patterns in a word. Like, if I would like to search for "microsoft" and I type in "soft", I would like it to retrieve "microsoft". For now, it only retrieves "microsoft", if I type in the first few words.

I am not the world's greatest explainer. Not sure if I am making myself clear here, but yeah..there you go.

I apologize for all the questions. I am a brand new access user and all these problems are very frustrating.

[orange]

Please post the query sql.

[accessnewb]

SELECT Contacts.ContactName, Contacts.ItemNo, Contacts.NDC
FROM Contacts
WHERE (((Contacts.ContactName) Like [Forms]![F_Contacts_Search].[txtContactName] & "*") AND ((Contacts.ItemNo) Like [Forms]![F_Contacts_Search].[txtItemNo] & "*") AND ((Contacts.NDC) Like [Forms]![F_Contacts_Search].[txtNDC] & "*");

[orange]

When you have a Like as follows:
suppose Somefield has value "QUE"
Like Somefield & "*" you are limiting responses to "QUE "+ zero or more characters

You could find Question questions query etc, but
you would not find requestion request requery since the first few characters do not match "Somefield" [que]

To find these fields you need to adjust your Like to this format (using the same example)

Like "*" & Somefield & "*"

Now you will find all of the examples

Question questions query requestion request requery
because you are requesting any string that contains "somefield" (que) anywhere in the string

Good luck.

[accessnewb]

@orange: thanks a ton! It worked!

Now what do I do about the blank fields? My query is executed only when all the fields for a column have values in them

[June7]

You want to return record even if no value? Assuming you don't permit empty strings in fields, add Or Is Null to the criteria for each field.

[accessnewb]

@June7:

Imagine I have 3 fields called FirstName, MiddleName and LastName. For the FirstName and LastName columns I have entered the names "John" and "Kennedy" respectively. I have left the MiddleName field blank. Now, in the search criteria if I enter "John" in the FirstName textbox and hit enter, "John Kennedy" is not retrieved because MiddleName field is blank.

[orange]

I fyou are using the base query you showed earlier

Where A like *"s1"* and B like *"s2"* and C like *"s3"*, then perhaps you should use

as june1 said

Where (A like *"s1"* or A is null) and (B like *"s2"* or b Is Null) and (C like *"s3"* or C is null)

[June7]

Did you try the Or Is Null? Seems that was the solution for another poster I helped. I actually don't have any search forms set up like this. I use VBA code to build a filter string with specific values selected from comboboxes, no Like operators involved, just haven't encountered the need in my projects.

[accessnewb]

maybe what I have done is totally wrong, but based on your inputs this is the query I came up with:

SELECT Contacts.ContactName, Contacts.ItemNo, Contacts.NDC
FROM Contacts
WHERE (((Contacts.ContactName) Like (([Contacts].[ContactName])="*" & [Forms]![F_Contacts_Search].[txtContactName] & "*" Or [Forms]![F_Contacts_Search].[txtContactName] Is Null)) AND ((Contacts.ItemNo) Like (([Contacts].[ItemNo])="*" & [Forms]![F_Contacts_Search].[txtItemNo] & "*" Or [Forms]![F_Contacts_Search].[txtItemNo] Is Null)) AND ((Contacts.NDC) Like (([Contacts].[NDC])="*" & [Forms]![F_Contacts_Search].[txtNDC] & "*" Or [Forms]![F_Contacts_Search].[txtNDC] Is Null)));


Something must be wrong in the above query. Cos now I get no results when I query this!

[orange]

maybe what I have done is totally wrong, but based on your inputs this is the query I came up with:

SELECT Contacts.ContactName, Contacts.ItemNo, Contacts.NDC
FROM Contacts
WHERE (((Contacts.ContactName) Like (([Contacts].[ContactName])="*" & [Forms]![F_Contacts_Search].[txtContactName] & "*" Or [Forms]![F_Contacts_Search].[txtContactName] Is Null)) AND ((Contacts.ItemNo) Like (([Contacts].[ItemNo])="*" & [Forms]![F_Contacts_Search].[txtItemNo] & "*" Or [Forms]![F_Contacts_Search].[txtItemNo] Is Null)) AND ((Contacts.NDC) Like (([Contacts].[NDC])="*" & [Forms]![F_Contacts_Search].[txtNDC] & "*" Or [Forms]![F_Contacts_Search].[txtNDC] Is Null)));


Something must be wrong in the above query. Cos now I get no results when I query this!

The problem is the =. It isn't equal , it's LIKE

I have adjusted your Where, but please check the bracketing,

WHERE ((Contacts.ContactName) Like "*" & [Forms]![F_Contacts_Search].[txtContactName] & "*" Or ([Forms]![F_Contacts_Search].[txtContactName] Is Null)) AND
((Contacts.ItemNo) Like "*" &
Forms]![F_Contacts_Search].[txtItemNo] & "*" Or ([Forms]![F_Contacts_Search].[txtItemNo] Is Null)) AND
((Contacts.NDC) Like "*" & [Forms]![F_Contacts_Search].[txtNDC] & "*" Or [Forms]![F_Contacts_Search].[txtNDC] Is Null));

[accessnewb]

yayy! it works!! thanks :-) phew!

[accessnewb]

alright! now I have only tiny last question. i already asked this in the other forum but I think I didn't make myself clear. The query works super now, and for the onClick event of the search button, this is the code I have written:

Private Sub searchTable_Click()
DoCmd.OpenQuery "Q_Contacts", acViewNormal

End Sub

The problem is I have to close the results window every time to search for new criteria. For example, I type "John" under first name and a results table opens with 5 results. I have to close the results table if I need to search for "Mathew", else the results table with "John" is shown.


This technically doesn't fall under the "query" category. Should I delete it here and re-post in the appropriate forum??