Help with wildcards

[lushh]

hi guys.. i want to include a search form in my database.. here is my current SQL statement:

SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd
FROM tblCompany
WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));

but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.

thanks a lot and good day...

 

[jwhite]

Two ways to achieve this:

1. Let the User know that they can place a * before and/or after the text to search for -- typing ABC* would return ABC and ABC Company; typing ABC would return only ABC.

2. Programmitically include the * before/after the string to find. This would force a wildcard search on every string, and return records that have ABC anywhere in the field.

The * is SQL's wildcard search identifier.

 

[lushh]

where should i insert the *? sorry i am just a newbie...

 

[jwhite]

I'm a newbie too!

If you pick 1., you don't have to program anything. Where the user would type the string to search for, they can put the * before and/or after that string to do a wildcard search.

If you pick 2., then you have to do alittle programming. In my trials, I added the * before and after the string to search for:

strtemp = Chr(34) & "*" & Me!txtSearchProjects & "*" & Chr(34)

Then I would include the variable [strtemp] within the SQL statement, and then execute the statement. In my example below, I am inserting the wildcards before/after, and then building a SQL statement to search all applicable fields within the table. The on-the-fly SQL statement is then assigned to the RecordSource of the subform. Finally, it does a requery on the subform to execute the new SQL statement generated:

    strtemp = Chr(34) & "*" & Me!txtSearchProjects & "*" & Chr(34)
    strSQLWHERE = "WHERE ProjectName Like " & strtemp & " OR AssignedTo Like " & strtemp & " OR Frequency Like " & strtemp & " OR Status Like " & strtemp
        
    Me!frmProjectSub1.Form.RecordSource = "SELECT * FROM tblProjects " & strSQLWHERE & ";"
    Me!frmProjectSub1.Requery

Hope that helps.