Searching with stLinkCriteria

[NHAnimator]

Hello,

I have the following script which I need to modify:

Private Sub Command231_Click()
On Error GoTo Err_Command231_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Main Table"
 
    stLinkCriteria = "[CaseName]=" & Me![NameSearch] 
    If IsNull([Forms]![Menu]![NameSearch]) Then
        MsgBox "You actually need to enter information first. Weird, huh?"
    Else
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.Close acForm, "Menu", acSaveNo
    End If
 
Exit_Command231_Click:
    Exit Sub
 
Err_Command231_Click:
    MsgBox Err.Description
    Resume Exit_Command231_Click
 
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

[cyberdwarf]

Use the LIKE keyword, instead of "="
Something like:-

stLinkCriteria = '[CaseName] Like('" & Forms!Menu![NameSearch] & %') Or [InsuredName] Like('" & Forms!Menu![NameSearch] & "%)'"

HTH

Steve

[FishVal]

Use the LIKE keyword, instead of "="
Something like:-

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

[cyberdwarf]

Sorry, Fish. I assumed "straight" SQL ;-)

Steve

[FishVal]

;-)

Apologizes not needed.

[kcddoorman]

subscribing here Ignore me.