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

Partial Search

P: 9
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSite_Enter()
  2.  
  3. Dim strSql As String
  4.  
  5. strSql = "Select * From ServiceRec Where SiteAddress = '" & Me!txtSite & "'"   
  6. Me.ServiceRecSubform.Form.RecordSource = strSql
  7.  
  8. ServiceRecSubform.Visible = True
  9. info1Subform.Visible = False
  10.  
  11.  
  12. End Sub
How should i edit the code? Thanks
Oct 31 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi Evanescent. Partial string matching is straightforward - use the Like operator and precede and follow your match string with the wildcard character, the asterisk:

Expand|Select|Wrap|Line Numbers
  1. strSql = "Select * From ServiceRec Where SiteAddress Like '*" & Me!txtSite & "*'"
-Stewart
Oct 31 '08 #2

ADezii
Expert 5K+
P: 8,597
You may also wish to execute your code only if a value actually exists in txtSite:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSite_Enter() 
  2. Dim strSql As String 
  3.  
  4. If Not IsNull(Me![txtSite]) Then
  5.   strSql = "Select * From ServiceRec Where ServiceRec.SiteAddress " & _
  6.            "Like '*" & Me!txtSite & "*'"
  7.   Me.RecordSource = strSql
  8.   Me.ServiceRecSubform.Form.RecordSource = strSql 
  9.  
  10.   ServiceRecSubform.Visible = True 
  11.   info1Subform.Visible = False 
  12. End If
  13. End Sub
Nov 1 '08 #3

P: 9
Thanks a lot for the reply. It works =)
Nov 1 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Nov 1 '08 #5

Post your reply

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