General discussion

Locked

VB Find Statement

By MsDelaine ·
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

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VB Find Statement

by andrew In reply to VB Find Statement

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.

Collapse -

VB Find Statement

by MsDelaine In reply to VB Find Statement

sorry code did not help me

Collapse -

VB Find Statement

by andrew In reply to VB Find Statement

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.

Collapse -

VB Find Statement

by MsDelaine In reply to VB Find Statement

Poster rated this answer

Collapse -

VB Find Statement

by RSV In reply to VB Find Statement

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...:)

Collapse -

VB Find Statement

by MsDelaine In reply to VB Find Statement

This code did not work for me

Collapse -

VB Find Statement

by sl-campbell In reply to VB Find Statement

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)

Collapse -

VB Find Statement

by sl-campbell In reply to VB Find Statement

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.

Collapse -

VB Find Statement

by MsDelaine In reply to VB Find Statement

Poster rated this answer

Collapse -

VB Find Statement

by prashant_bajaj In reply to VB Find Statement

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

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums