Search using alternative multiple fields

[JimMurray]

I have an Access/SQL database that I want to reduce to an Access only demo
/small user model. Except for the Search procedure, I'm there. The search
procedure uses vb on the Access side then passes variables to a stored
procedure. I've tried to combine the two but I'm making too many errors. I've
written this for the Access:

Dim strSql As String, strWHERE As String, strORDER As String
Private Sub cmdSearch_Click()
On Error GoTo Err_Search_Click
strSql = "tblPersons.PersonsID, tblPersons.BirthDate, tblPersons.SSN,
tblPersons.SID, tblPersons.License, tblPersons.JailStatus,
tblPersons.PersonType, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] &
' ' & [Suffix] AS LastNameFirst, [Race] & '/' & [Gender] AS RG FROM
tblPersons "
strORDER = "ORDER BY LastNameFirst, BirthDate "
strWHERE = "WHERE "
If IsNull(txtLastName) Then
strWHERE = strWHERE
ElseIf Right(txtLastName, 1) = "*" Then
strWHERE = strWHERE & Left(txtLastName, Len(txtLastName) - 1) & "
AND "
Else
strWHERE = strWHERE & "LastNameSoundEx=" & txtLastNameSoundex & "
AND "
End If

If IsNull(txtFirstName) Then
strWHERE = strWHERE
ElseIf Right(txtFirstName, 1) = "*" Then
strWHERE = strWHERE & Left(txtFirstName, Len(txtFirstName) - 1)
Else
strWHERE = strWHERE & "FirstNameSoundEx=" & txtFirstNameSoundex
End If

If IsNull(txtSSN) Then
strWHERE = strWHERE
Else
strWHERE = strWHERE & txtSSN
End If

If IsNull(txtSID) Then
strWHERE = strWHERE
Else
strWHERE = strWHERE & txtSID
End If
If strWHERE = "WHERE " Then
strWHERE = ""
End If
len1 = Datalength(strWHERE)
strWHERE = SubString(strWHERE, 1, len1 - 5)


strSql = strSql & strWHERE
Set myDB = CurrentDb
Set myQuery = myDB.QueryDefs("QRYSearchPersonsAlias")
myQuery.SQL = strSql
myQuery.Close
myDB.Close
Forms!SearchDefendant!searchDefendantResults.SourceObject =
"searchDefendantResults"
Me!searchDefendantResults.Form.RecordSource = "QRYsearchPersonsAlias"
Exit_Search_Click:
Exit Sub
Err_Search_Click:
Beep
MsgBox Error$
Resume Exit_Search_Click
End Sub

The former SQL language was:
CREATE PROCEDURE searchPersonsAlias
(@LastName varchar(20) =NULL,
@LastNameSoundEx varchar(4) =NULL,
@FirstName varchar(20) =NULL,
@FirstNameSoundEx varchar(20) =NULL,
@SSN varchar(9) =NULL,
@SID varchar(10) =NULL)
AS
DECLARE @Sel1 varchar(800),@Order1 varchar(255)
DECLARE @Where1 varchar(500), @len1 int
SELECT @Sel1 = 'SELECT DISTINCT PersonsID, ISNULL(LastName + SPACE(1) +
Suffix, ISNULL(LastName, '' '')) + '', '' + ISNULL(FirstName, '' '') AS
LastNameFirst, BirthDate, Race + ''/'' + Gender AS RG, SSN, SID, License,
JailStatus, PersonType FROM PersonsAlias_VIEW daf'
SELECT @Order1 = ' ORDER BY LastNameFirst, BirthDate '
SELECT @Where1 = ' WHERE '
IF @LastName <> ''
SELECT @Where1 = @Where1 + 'LastName Like ''' + SubString(@LastName, 1,
DataLength(@LastName)) + '%'' AND '
IF @LastNameSoundEx <>''
SELECT @Where1 = @Where1 + 'LastNameSoundEx=''' + @LastNameSoundEx +
''' AND '
IF @FirstName <> ''
SELECT @Where1 = @Where1 + 'FirstName Like ''' + SubString(@FirstName,
1, DataLength(@FirstName)) + '%'' AND '
IF @FirstNameSoundEx <> ''
SELECT @Where1 = @Where1 + 'FirstNameSoundEx=''' + @FirstNameSoundEx +
''' AND '
IF @SSN <> ''
SELECT @Where1 = @Where1 + 'SSN=''' + @SSN + ''' AND '
IF @SID <> ''
SELECT @Where1 = @Where1 + 'SID=''' + @SID + ''' AND '


SELECT @LEN1 = Datalength(@Where1)
SELECT @Where1 = SubString(@Where1,1,@len1-5)
PRINT @Sel1
PRINT @Where1
PRINT @Order1
Exec (@Sel1 + @Where1 + @Order1)
GO

The former VB Language was:
'strSql = "SearchPersonsAlias "
strWHERE = ""
If IsNull(txtLastName) Then
strWHERE = strWHERE & "'','',"
ElseIf Right(txtLastName, 1) = "*" Then
strWHERE = strWHERE & "'" & Left(txtLastName, Len(txtLastName) - 1)
& "',"
strWHERE = strWHERE & "'',"
Else
strWHERE = strWHERE & "'',"
strWHERE = strWHERE & "'" & txtLastNameSoundex & "',"
End If
If IsNull(txtFirstName) Then
strWHERE = strWHERE & "'','',"
ElseIf Right(txtFirstName, 1) = "*" Then
strWHERE = strWHERE & "'" & Left(txtFirstName, Len(txtFirstName) - 1)
& "',"
strWHERE = strWHERE & "'',"
Else
strWHERE = strWHERE & "'',"
strWHERE = strWHERE & "'" & txtFirstNameSoundex & "',"
End If
If IsNull(txtSSN) Then
strWHERE = strWHERE & "'',"
Else
strWHERE = strWHERE & "'" & txtSSN & "',"
End If
If IsNull(txtSID) Then
strWHERE = strWHERE & "''"
Else
strWHERE = strWHERE & "'" & txtSID & "'"
End If
If strWHERE = "'','','','','',''" Then
strWHERE = "'%','','','','',''"
End If
strSql = strSql & strWHERE
Set myDB = CurrentDb
Set myQuery = myDB.QueryDefs("QRYSearchPersonsAlias")
myQuery.SQL = strSql
myQuery.Close
myDB.Close

Thanks.