Partial Search

[Evanescent]

Hi, I need help regarding partial search. Currently i have a textbox named txtSite and when i press enter, it retrieves records from the table "ServiceRec" whereby the value for "SiteAddress" = the value typed in "txtSite". It works fine but i want the user to be able to search for records even if they do not type the address in full.

Currently, my code is as follows:

Private Sub txtSite_Enter()
 
Dim strSql As String
 
strSql = "Select * From ServiceRec Where SiteAddress = '" & Me!txtSite & "'"   
Me.ServiceRecSubform.Form.RecordSource = strSql
 
ServiceRecSubform.Visible = True
info1Subform.Visible = False
 
 
End Sub

How should i edit the code? Thanks

[Stewart Ross]

Hi Evanescent. Partial string matching is straightforward - use the Like operator and precede and follow your match string with the wildcard character, the asterisk:

strSql = "Select * From ServiceRec Where SiteAddress Like '*" & Me!txtSite & "*'"

-Stewart

[ADezii]

You may also wish to execute your code only if a value actually exists in txtSite:

Private Sub txtSite_Enter() 
Dim strSql As String 
 
If Not IsNull(Me![txtSite]) Then
  strSql = "Select * From ServiceRec Where ServiceRec.SiteAddress " & _
           "Like '*" & Me!txtSite & "*'"
  Me.RecordSource = strSql
  Me.ServiceRecSubform.Form.RecordSource = strSql 
 
  ServiceRecSubform.Visible = True 
  info1Subform.Visible = False 
End If
End Sub

[Evanescent]

Thanks a lot for the reply. It works =)

[missinglinq]

I've got to ask ***how*** this code works! I understand how the SQL statement works, but how does having the code in the

Private Sub txtSite_Enter()

event work?

This event fires when the user tabs into the txtSite textbox, not when the <Enter> key is pressed! You'd have to enter your search string, exit the textbox, then re-enter the textbox to trigger the code.

Linq ;0)>