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

Searching with stLinkCriteria

P: 1
Hello,

I have the following script which I need to modify:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command231_Click()
  2. On Error GoTo Err_Command231_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "Main Table"
  8.  
  9.     stLinkCriteria = "[CaseName]=" & Me![NameSearch] 
  10.     If IsNull([Forms]![Menu]![NameSearch]) Then
  11.         MsgBox "You actually need to enter information first. Weird, huh?"
  12.     Else
  13.         DoCmd.OpenForm stDocName, , , stLinkCriteria
  14.         DoCmd.Close acForm, "Menu", acSaveNo
  15.     End If
  16.  
  17. Exit_Command231_Click:
  18.     Exit Sub
  19.  
  20. Err_Command231_Click:
  21.     MsgBox Err.Description
  22.     Resume Exit_Command231_Click
  23.  
  24. End Sub
Essentially, I need two things: (1) Change the criteria from an exact match to a partial text match and (2) search not only on the CaseName field, but also on the InsuredName field at the same time.

Suggestions?

Thanks much in advance,
NHAnimator
Sep 25 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 218
Use the LIKE keyword, instead of "="
Something like:-
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = '[CaseName] Like('" & Forms!Menu![NameSearch] & %') Or [InsuredName] Like('" & Forms!Menu![NameSearch] & "%)'"
HTH

Steve
Sep 25 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Use the LIKE keyword, instead of "="
Something like:-
Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = '[CaseName] Like('" & Forms!Menu![NameSearch] & %') Or [InsuredName] Like('" & Forms!Menu![NameSearch] & "%)'"
HTH

Steve
Wildcard '%' is a part of ANSI SQL. Access by default supports its own dialect where '*' is used for the same purpose unless SQL syntax is set to ANSI.
See also Issue with Select not returning data to recordset

Regards,
Fish
Sep 25 '07 #3

Expert 100+
P: 218
Sorry, Fish. I assumed "straight" SQL ;-)

Steve
Sep 25 '07 #4

FishVal
Expert 2.5K+
P: 2,653
;-)

Apologizes not needed.
Sep 25 '07 #5

P: 76
subscribing here Ignore me.
Sep 25 '07 #6

Post your reply

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