Discussion Locked

VB Find Statement

0 helpful
12 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here is my find statement to an access database from VB 6. I want the find to be case insensitive.
Help?

'Search the file until the user is found
rsWork.MoveFirst
rsWork.Find "Project = '" & cboSelection & "'" If Not rsWork.EOF Then
If rsWork!Project = cboSelection.Text Then
'When found, retreive information

12 replies

by andrew

You didn't say what problem you were having or if there is an error.

Your code looks fine (although the comment says search for User, but you do a Find on Project)

For find to work, you need a cursor that can scroll in both directions. The default (adOpenForwardOnly) cannot. I recommend adOpenStaic for a read-only use like this.

Here's the test code I wrote that works ok:

Dim oCon As ADODB.Connection
Dim RsWork As ADODB.Recordset

Set oCon = New ADODB.Connection
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;"
Set RsWork = New ADODB.Recordset
RsWork.Open "SELECT Key,Project FROM tProject;", oCon, adOpenStatic, adLockReadOnly

'Search the file until the user is found
RsWork.MoveFirst
RsWork.Find "Project = '" & cboSelection & "'"
If Not RsWork.EOF Then
MsgBox "Got it!"
Else
MsgBox "Don't got it!"
End If

Hope this helps.

by andrew

One last thought:

Your test:

If rsWork!Project = cboSelection.Text

is redundant. If the Find operation succeeds (which you test for with 'If Not rsWork.EOF') , this will always be true.

by rsv

HI,
To make find case insensitive, try doing like this.....

rsWork.Find "ucase(Project) = '" & ucase(cboSelection) & "'"

This is to make it upper case of both the values.

Hope this is what u r looking for.
Good Luck...:)

by sl-campbell

As far as I know searches on an Access database are not case sensitive. Searches on only certain types of data sources are case sensitive but not an MDB. Whether a search can be case sensitive is definded by the database.
You have to create an extension to your search...at best create a function that gets passed to it the recordset, field to search on, criteria for the search, and a value to identify if the search is to be case sensitive. That way you are not duplicating code. And then use thefollowing code:

'Search the file until the Criteria is found
bRecordFound = False
rsWork.MoveFirst

rsWork.Find "Project = '" & cboSelection & "'"
bRecordFound = Not rsWork.NoMatch
If bRecordFound Then
bRecordFound = False

Do Until rsWork.EOF

strFindResults = rsWork!Project
bRecordFound = StrComp(strFindResults, cboSelection, vbBinaryCompare)

If Not bRecordFound Then
rsWork.FindNext
Else
Exit Do
End If

Loop

End If

'Here assign the Functions return value to strFindResults
If bRecordFound Then
'When found, retreive information

Another way would be to clone or copy the Recordset, set its' Filter to cboSelection and Requery it. Then use a 'Do Until .EOF' Loop and use the same comparison code as above, using MoveNext if there was no match.
(Note: The Filter method is only for a DAO/Jet Recordset)

by sl-campbell

One factor I forgot to mention: If the FindFirst FOUND records and the StrComp function did NOT find any identical records, you may need to move back to the previous record, (if the rs is used elsewhere such as in as the recordsource for a bound control). If that is the case, then at the begining of the function, either set a variable(variant) to the Bookmark of the current record and later set the Bookmark to this variable, or, use a variable to identify the current record if it has a unique key and use this variable in a FindFirst to move back to the previous record.

by prashant_bajaj

Dear MsDelaine,
I hope this will work for u.. The line where u are actually comparing the Database Field use the UCase$ function of VB.

Prashant Bajaj

This discussion is closed to new replies.