I wrote my SQL Query and I am using the “%” wildcard and I do not think it is working properly. My query looks like this.
stSQL = “SELECT * FROM ““Activity Report”” WHERE NameLast = '” & searchVal & “'”
Which produces this when executed: SELECT * FROM “Activity Report” WHERE NameLast = ‘D%’
But when I get down to Do While Not adoRst.EOF it is already set to “True” and then steps out of the while loop.
If I run this query: stSQL = “SELECT * FROM ““Activity Report””” Do While Not adoRst.EOF is “False” and to loops through every record and displays them correctly.
Anybody see any reason why the wildcard does not work?
Any help with this would be grealy appreciated.
Below is my code for clicking the search button and getting the data.
Private Sub butSearch_Click()
If ufDBLookup.txtLName.Value = “” Then
MsgBox "You must enter a last name.", vbOKOnly, "No Name entered"
Exit Sub
End If
Call GetData(“person_last_name”, ufDBLookup.txtLName.Text & “%”)
End Sub
Private Sub GetData(searchField As String, searchVal As String)
On Error GoTo ErrHnd
Dim adoCN As ADODB.Connection
Dim adoRst As ADODB.Recordset
Dim stSQL As String
Dim DResults As String
Dim LName As String
ufDBLookup.lbDBResults.ColumnWidths = “0”
LName = ufDBLookup.txtLName.Value
Set adoCN = New ADODB.Connection
adoCN.Open (“Driver={Lotus NotesSQL Driver (*.nsf)};Database=hp56p001.nsf;Server=Local;”)
stSQL = “SELECT * FROM ““Activity Report”” WHERE NameLast = '” & searchVal & “'”
Set adoRst = adoCN.Execute(stSQL)
DResults = “”
Do While Not adoRst.EOF
Dim sufx As String
Select Case adoRst.Fields("NameSuffix")
Case Is = "J"
sufx = "Jr."
Case Is = "S"
sufx = "Sr."
Case Is = "1"
sufx = "I"
Case Is = "2"
sufx = "II"
Case Is = "3"
sufx = "III"
Case Is = "4"
sufx = "IV"
End Select
DResults = adoRst.Fields(“NameLast”) & ", " & adoRst.Fields(“NameFirst”) & " " & adoRst.Fields(“NameMI”) & " " & sufx & " " & Chr(124) & " DOB: " & adoRst.Fields(“DOB”) & " " & Chr(124) & " Sex: " & adoRst.Fields(“Sex”) & " " & Chr(124) & " Date: " & Left(adoRst.Fields(“Date”), 10)
ufDBLookup.lbDBResults.AddItem adoRst.Fields(“DocID”)
ufDBLookup.lbDBResults.List(i, 1) = DResults
sufx = ""
i = i + 1
adoRst.MoveNext
Loop
If DResults = “” Then
DResults = "No Results for Last Name of '" & ufDBLookup.txtLName.Value & "'"
ufDBLookup.lbDBResults.AddItem
ufDBLookup.lbDBResults.List(i, 1) = DResults
End If
SortListBox ufDBLookup.lbDBResults
Exit Sub
ErrHnd:
If Err.Number = “94” Then
Resume Next
Else
HandleLookupErrors
End If
End Sub