[RESOLVED] a little help in case sensitivity

[carlQT]

in my program, i have a textbox that specifies what data i will be searching in the database and diplaying it in the flexgrid... my problem is that it is case sensitive. How can i make the searching case insensitive? any ideas!?

[Shuja Ali]

What database are you using. I don't think case sensitivity is provided by default in any database. I guess you are missing something.

[Hack]

Use either UCase or LCase whichever would be the most appropriate.

[carlQT]

i'm using ms access 1997

[Hack]

It would probably be beneficial to know what is actually (in terms of case) being stored. All upper? All lower? Mixture? Provide an example.

[carlQT]

o i c... it's a mixture in the database... So when I searched for the word "Wheels" it displays all columns with related to "Wheels" however "wheels" doesn't return anything at all

[aikidokid]

When you do a search, search for UCase("Wheels") and use the same for what is returned from the database,
UCase(RSet.Fields("FieldName").Value)

[carlQT]

thank you very much... but how do I use it in an SQL statement?...
here is my code for searching

Data1.RecordSource _
= "SELECT * FROM AERO " _
& "Where Description = '" & Text1.Text & " '"

[aikidokid]

Well you would change Text1.Text to UCase(Text1.Text)
and then I think you would so something like this;

Data1.RecordSource _
= "SELECT * FROM AERO " _
& "Where Description = '" & UCase(Text1.Text) & " '"

UCase(Data1.RecordSource.Fields("FieldName").Value)

I haven't tested this, and I'm not too sure, but give it a go

[carlQT]

well it didn't work but still thank you so much for the idea... guess I have no choice but to use a looping statement^^

[aikidokid]

If it helps, this is some code I have used for searching in the past and adding results to a listbox, using Access and ADO.
You would just have to add the 'UCase' to it:

strSQL = "SELECT * FROM  tbl_Code"
strSQL = strSQL & " WHERE 'N' & NodeID = '" & tmpCat & "'"
strSQL = strSQL & " And (Code_Description LIKE '%" & strSearch & "%' OR Code_Text LIKE '%" & strSearch & "%')"
    

End If
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
Do While Not rs.EOF
        With lstSearch
            .AddItem rs.Fields("Code_Description")
            ctr = ctr + 1
        End With
    rs.MoveNext
Loop

rs.Close