ACC2000: Can't do AND Search using 2 search keys?

[timhysniu]

Hello group,

I am trying to search using several search keys. In my case I want t
search by Last Name and First Name.
Heres the code that I am using: (on Query x: Last Name Sorted (ASC)
First Name Sorted (ASC) )

DoCmd.ShowAllRecords
DoCmd.GoToRecord , , acFirst

If strLast <> "" Then
DoCmd.GoToControl "Last Name"
DoCmd.FindRecord strLast, acEntire, , acDown, , acCurrent
First_Name.SetFocus
End If

If strFirst <> "" Then
DoCmd.GoToControl "First Name"
DoCmd.FindRecord strFirst, acEntire, , acDown, , acCurrent
End If




What is happening is the Last name "????" is found. Since they ar
sorted by last name first, I am assuming that searching further dow
will find the first name "????". However, the cursor seems to b
starting from the beginning again. Can someone please give me th
simplest approach to do 1 AND Search using 2 columns? (First Name an
Last Name)

Thank You,
ti

 

[Dirk Goldgar]

Hello group,

I am trying to search using several search keys. In my case I want to
search by Last Name and First Name.
Heres the code that I am using: (on Query x: Last Name Sorted (ASC),
First Name Sorted (ASC) )

DoCmd.ShowAllRecords
DoCmd.GoToRecord , , acFirst

If strLast <> "" Then
DoCmd.GoToControl "Last Name"
DoCmd.FindRecord strLast, acEntire, , acDown, , acCurrent
First_Name.SetFocus
End If

If strFirst <> "" Then
DoCmd.GoToControl "First Name"
DoCmd.FindRecord strFirst, acEntire, , acDown, , acCurrent
End If




What is happening is the Last name "????" is found. Since they are
sorted by last name first, I am assuming that searching further down
will find the first name "????". However, the cursor seems to be
starting from the beginning again. Can someone please give me the
simplest approach to do 1 AND Search using 2 columns? (First Name and
Last Name)

Thank You,
tim

I'm not sure why the FindRecord method isn't working the way you expect,
but rather than try to figure that out, I'll suggest the way I would do
the search for both first and last name at once. Here's what the code
would look like:

'----- start of example code -----
Dim strCriteria As String

If Len(strLast) > 0 Then
strCriteria = strCriteria & _
"AND [Last Name] = " & Chr(34) & strLast & Chr(34)
End If

If Len(strFirst) > 0 Then
strCriteria = strCriteria & _
"AND [First Name] = " & Chr(34) & strFirst & Chr(34)
End If

strCriteria = Mid(strCriteria, 5) ' Trim off leading "AND ".

With Me.RecordsetClone
.FindFirst strCriteria
If .NoMatch Then
MsgBox "No match found for " & strCriteria & "."
Else
Me.Bookmark = .Bookmark
End If
End With
'----- end of example code -----