|
 |
access thread: Searching for near matches
Message #1 by "Bob Bedell" <bdbedell@m...> on Fri, 16 Mar 2001 00:34:54
|
|
I folks. I have a searching problem. I have several forms with a Search
command button on them. When I click cmdSearch, frmSearch opens and allows
me to enter client first and last name search parameters in two unbound
text boxes (txtClientLastName and txtClientFirstName). When I click cmdOK
on frmSearch, the code below executes and sets the calling forms record
source to the specified SQL statement.
Private Sub cmdOK_Click()
'Requery CallingForm based on TextBox values.
Dim CallingForm As String
Dim strSQL As String
CallingForm = Me.OpenArgs
DoCmd.OpenForm CallingForm
'Contruct SQL statement for CallingForm's RecordSource property.
With Forms(CallingForm)
If Len(Me!txtLastName & "") > 0 And Len(Me!txtFirstName & "") > 0 Then
If Forms(CallingForm).Name = "frmReferrals" Then
strSQL = "SELECT * FROM qryReferrals "
strSQL = strSQL & "WHERE ClientLastName LIKE '" & Me!
txtLastName & "'"
strSQL = strSQL & "AND ClientFirstName LIKE '" & Me!
txtFirstName & "'"
End If
.RecordSource = strSQL
End If
End With
DoCmd.Close acForm, "frmSearch"
A couple of Else statments in the finished code allow me to reuse
frmSearch for several different calling forms. Problem is:
If the SQL returns an empty recordset (i.e., no EXACT MATCHES for my
search parameters are found), how can I then requery for NEAR MATCHES, and
assign a "near match" recordset as the calling forms record source. I'd
need to use code that evaluates the first 3 or 4 letters of txtLastName,
followed by some kind of wild card character. But I'm way over my head
here.
Staff are notorious for mispelling client names and I want to avoid
assigning multiple ClientID numbers to the same client.
Any thoughts? Thanks in advance.
Message #2 by Brian Skelton <brian_skelton@o...> on Fri, 16 Mar 2001 11:14:17 GMT
|
|
Bob
The extra code below assigns your SQL to a temporary
recordset to allow you to test if any records are returned.
If the recordset is empty both EOF and BOF will be true, so
you build another SQL statement using the '*' wildcard. Again
the recordset is tested. The Recordsource is changed only if
the new recordset contains records.
BDS
Dim rstTest as Recordset
Dim MyDB as Database
set MyDB = CurrentDB
'Contruct SQL statement for CallingForm's RecordSource
property.
With Forms(CallingForm)
If Len(Me!txtLastName & "") > 0 And Len(Me!txtFirstName &
"") > 0 Then
If Forms(CallingForm).Name = "frmReferrals" Then
strSQL = "SELECT * FROM qryReferrals "
strSQL = strSQL & "WHERE ClientLastName LIKE '" &
Me!txtLastName & "'"
strSQL = strSQL & "AND ClientFirstName LIKE '" &
Me!txtFirstName & "'"
End If
Set rstTest = MyDB.OpenRecordse(strSQL,dbOpenDynaset,
dbReadOnly)
'Check if recordset returns any records
If Not(rstTest.EOF And rstTest.BOF) then
.RecordSource = strSQL
else
Set rstTest = Nothing
'Rebuild SQL, this time taking first four
characters of last name and using the '*' wildcard
strSQL = "SELECT * FROM qryReferrals "
strSQL = strSQL & "WHERE ClientLastName LIKE '" &
left$(Me!txtLastName,4) & "*'"
Set rstTest = MyDB.OpenRecordset(strSQL,
dbOpenDynaset, dbReadOnly)
'Check if recordset returns any records
If Not(rstTest.EOF And rstTest.BOF) then
.RecordSource = strSQL
endif
Set rstTest = Nothing
endif
End If
End With
---- Original message ----
>Date: Fri, 16 Mar 2001 00:34:54
>From: "Bob Bedell" <bdbedell@m...>
>Subject: [access] Searching for near matches
>To: "Access" <access@p...>
Message #3 by "Bob Bedell" <bdbedell@m...> on Tue, 20 Mar 2001 15:33:11
|
|
Hi Brian,
Just wanted to say thanks for the help. Code does exactly what I need it
to do. Thanks Again.
> Bob
>
> The extra code below assigns your SQL to a temporary
> recordset to allow you to test if any records are returned.
> If the recordset is empty both EOF and BOF will be true, so
> you build another SQL statement using the '*' wildcard. Again
> the recordset is tested. The Recordsource is changed only if
> the new recordset contains records.
>
> BDS
>
> Dim rstTest as Recordset
> Dim MyDB as Database
>
> set MyDB = CurrentDB
>
> 'Contruct SQL statement for CallingForm's RecordSource
> property.
> With Forms(CallingForm)
> If Len(Me!txtLastName & "") > 0 And Len(Me!txtFirstName &
> "") > 0 Then
> If Forms(CallingForm).Name = "frmReferrals" Then
> strSQL = "SELECT * FROM qryReferrals "
> strSQL = strSQL & "WHERE ClientLastName LIKE '" &
> Me!txtLastName & "'"
> strSQL = strSQL & "AND ClientFirstName LIKE '" &
> Me!txtFirstName & "'"
> End If
>
> Set rstTest = MyDB.OpenRecordse(strSQL,dbOpenDynaset,
> dbReadOnly)
>
> 'Check if recordset returns any records
> If Not(rstTest.EOF And rstTest.BOF) then
> .RecordSource = strSQL
> else
> Set rstTest = Nothing
>
> 'Rebuild SQL, this time taking first four
> characters of last name and using the '*' wildcard
> strSQL = "SELECT * FROM qryReferrals "
> strSQL = strSQL & "WHERE ClientLastName LIKE '" &
> left$(Me!txtLastName,4) & "*'"
> Set rstTest = MyDB.OpenRecordset(strSQL,
> dbOpenDynaset, dbReadOnly)
> 'Check if recordset returns any records
> If Not(rstTest.EOF And rstTest.BOF) then
> .RecordSource = strSQL
> endif
> Set rstTest = Nothing
> endif
>
> End If
> End With
>
>
> ---- Original message ----
> >Date: Fri, 16 Mar 2001 00:34:54
> >From: "Bob Bedell" <bdbedell@m...>
> >Subject: [access] Searching for near matches
> >To: "Access" <access@p...>
|
|
|
View Cart
