Case insensitive search 1

[rickgalty]

Hello, I want to look up a name in adatabase. I've got it working, as long as the user enters the name just as it appears - ie entering "Smith" will return hits for "Smith", but not for "smith" or "SMITH". How do I allow this to find matching entries without regard to case?

Richard

 

[gmmastros]

What database are you using? If it's sql server, you can specify the collation in the where clause.

Select *
From Table
Where Field = 'Something' Collate SQL_Latin1_General_CP1_CI_AI

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickgalty]

Hi, no, I'm using a MS Access database.

 

[petermeachem]

Access isn't case sensitive is it.
How are you looking up the name?
"SELECT * FROM mytable
WHERE surname = '" & surnamevariable & "'"

should be the sort of thing you are doing.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[rickgalty]

Thanks for your assistance... here's the code I have on the "Find" button...

Private Sub btnFind_Click()
lblNoRecord.Visible = False
Dim moves As Integer
moves = 0
While Not txtEmpNo.Text = txtNumber.Text
If Inspectors.Recordset.EOF Then Inspectors.Recordset.MoveFirst Else Inspectors.Recordset.MoveNext
moves = moves + 1
If moves > Inspectors.Recordset.RecordCount Then lblNoRecord.Visible = True
If moves > Inspectors.Recordset.RecordCount Then txtEmpNo.Text = txtNumber.Text

Wend

End Sub

 

[rickgalty]

Whoops... wrong button - that's the one that finds by employee number - here's the code for the button that searches by name :-

Private Sub btnFindName_Click()
Inspectors.Recordset.MoveNext
lblNoName.Visible = False
Dim moves As Integer
moves = 0
While Not txtFindName.Text = LastName.Text
If Inspectors.Recordset.EOF Then Inspectors.Recordset.MoveFirst Else Inspectors.Recordset.MoveNext
moves = moves + 1
If moves > Inspectors.Recordset.RecordCount Then lblNoName.Visible = True
If moves > Inspectors.Recordset.RecordCount Then txtFindName.Text = LastName.Text

Wend
End Sub

 

[petermeachem]

That's a relatively unusual way to code something like that and can go wrong in various ways, for instance if you happen to be sitting on the last record when you enter the Sub.
How do you create the recordset?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[rickgalty]

Thanks for the help. The recordset is created when the form is loaded...

Private Sub Form_Load()

Dim rs As Recordset
Dim Locrs As Recordset
Dim Titrs As Recordset
Dim db As Database
Set db = OpenDatabase(App.Path & "\Employees.mdb")
Set rs = db.OpenRecordset("Employees")
Set Locrs = db.OpenRecordset("Locations")
Set Titrs = db.OpenRecordset("Titles")

End Sub

I used to do database programming with PHP / MySQL, but am trying to get this one set up in VB.

 

[petermeachem]

What I would do is :-
Private Sub btnFindName_Click()
Dim rs As Recordset
Set rs = db.OpenRecordset("SELECT * FROM Inspectors WHERE afield ='" & searchtext.Text & "'")
if not (rs.eof and rs.bof) then
txtSomefield.Text = rs("sometablecolumn")
lblNoName.Visible = false
else
lblNoName.Visible = True
endif
rs.close
end sub
Actually I can't see what you are trying to do. As far as I can see, you wind through the recordset and then set txtFindName.Text = LastName.Text
While Not txtFindName.Text = LastName.Text is not checking anything related to the recordset so won't be affected by the loop?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[rickgalty]

There a bunch of bound textboxes on the form, including "LastName". It winds through the recordset until the current records "LastName" field is the same as what the user has entered into "FindName", at which point it stops, and the current record is displayed in the bound text boxes.

It works as it should, except for discriminating between Smith, smith and SMITH.

The

Set rs = db.OpenRecordset("SELECT * FROM Inspectors WHERE afield ='" & searchtext.Text & "'")

Line will be helpfull though, thanks - I'm trying to figure out how to make VB use a SQL Statement instead of searching bound date text boxes anyway.

Can you tell me how one would connnect an "INSERT" SQL Statement to a command button on a VB form?

Richard

 

[petermeachem]

db.execute "INSERT INTO ..."

Using bound boxes is not exactly highly thought of round here!

I can't see why it should be case sensitive, it's not linked to Oracle or something odd like that is it?

 

[rickgalty]

Thanks, no, it's just a regular Access table, but the search is certainly only finding exact matches.

I'll look into redoing the form without bound data controls, and see how it goes. Thanks for everyone's help

Richard

 

[Golom]

You could just

While Not [COLOR=red]UCase([/color]txtFindName.Text[COLOR=red])[/color] = [COLOR=red]UCase([/color]LastName.Text[COLOR=red])[/color]

although I recommend the Find method for the recordset as in

Inspectors.Recordset.MoveFirst 
Inspectors.Recordset.Find "[LastName] = '" & txtFindName.Text & "'"

 

[petermeachem]

Of course, that is the problem. The code is checking two textboxes and not the table directly. Obvious when you see it.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/