Ignoring case on SELECT WHERE...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following command:

recSet3.Open "SELECT * from invtiv_PRINTER where PRINTER_MODEL = '" &
ws_printer_model & "'"

However, I want the SELECT to return a record on matching TEXT, not on
matching TEXT and CASE. So if I'm looking for (in my table):

"HP DeskJet 690C", and my ws variable contains:

"hp deskjet 690c", I want the record.

I have been unable to locate a solution to this issue in the MS help files.
Any ideas on what I need to do would really be appreciated.

Thanks!
 
Dennis said:
I have the following command:

recSet3.Open "SELECT * from invtiv_PRINTER where PRINTER_MODEL = '" &
ws_printer_model & "'"

However, I want the SELECT to return a record on matching TEXT, not on
matching TEXT and CASE. So if I'm looking for (in my table):

"HP DeskJet 690C", and my ws variable contains:

"hp deskjet 690c", I want the record.

I have been unable to locate a solution to this issue in the MS help
files. Any ideas on what I need to do would really be appreciated.

Thanks!
Click to expand...

So you're saying you want a case-insentive comparison? The reason
there's no help topic on how to get that is that all text comparisons in
queries -- in a Jet (MDB) database, anyway -- are normally
case-insensitive. You don't have to do anything in particular to get
"hp deskjet 690c" to compare as equal to "HP DeskJet 690C". If you're
getting results that suggest otherwise, either you're not querying a Jet
database, or you are mistaken as to the cause.
 
well, here's some more info:

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = h:\ITS PC Rollout
Project\ITS_Rollout_Tracking.mdb;" & _
"user id=;password=; , " & _
adOpenKeyset & "," & adLockPessimistic & "," & adCmdTableDirect

Set dbConn = New ADODB.Connection
dbConn.Open connStr
Set recSet = New ADODB.Recordset

recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic

recSet.ActiveConnection = CurrentProject.Connection

recSet3.Open "SELECT * from invtiv_PRINTER where PRINTER_MODEL = '" &
ws_printer_model & "'"

If recSet3.RecordCount > 0 Then
..
..
..
End if

When I entere lowercase text in the form (used in the WS variable), then use
it for the query, my "recSet3.RecordCount" is zero. However, If I use
matching case in that variable, it works (recordCount = 1). So either it's a
case issue, or there is some unprintable text in the record, in which case I
need to know how to wildcard a query.

Thanks!
 
Actually, the table is alinked Oracle table. And it's still not working.

Any ideas anyone?
 
How about trying this:

There is a VBA function called UCASE, working like this:
dim sometext as string

sometext = "my small text"
sometext = ucase(sometext)
Using ucase would set sometext = "MY SMALL TEXT".

In your query: "where ucase(printermodell) = ucase(ws_printer_modell)"

jokobe
 
Dennis said:
well, here's some more info:

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = h:\ITS PC Rollout
Project\ITS_Rollout_Tracking.mdb;" & _
"user id=;password=; , " & _
adOpenKeyset & "," & adLockPessimistic & "," &
adCmdTableDirect

Set dbConn = New ADODB.Connection
dbConn.Open connStr
Set recSet = New ADODB.Recordset

recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic

recSet.ActiveConnection = CurrentProject.Connection

recSet3.Open "SELECT * from invtiv_PRINTER where PRINTER_MODEL =
'" & ws_printer_model & "'"

If recSet3.RecordCount > 0 Then
.
.
.
End if

When I entere lowercase text in the form (used in the WS variable),
then use it for the query, my "recSet3.RecordCount" is zero. However,
If I use matching case in that variable, it works (recordCount = 1).
So either it's a case issue, or there is some unprintable text in the
record, in which case I need to know how to wildcard a query.
Click to expand...

That doesn't doesn't show me what the connection is for recSet3, since
you are opening it using CurrentProject.Connection, not the connection
whose ConnectString you posted.

Is this in an ADP?
 
Dennis said:
Actually, the table is alinked Oracle table. And it's still not
working.

Any ideas anyone?
Click to expand...

Maybe Oracle text comparisons are case sensitive. If so, jokobe's
suggestion of comparing the upcased text sounds like a solution, but it
will probably not be very efficient, as I expect it will force all
records to be brought across the network. If Oracle has a built-in
case-insensitive comparison function, or has its own version of the
UCase function, you may get better results by using a pass-through query
to use apply your criterion on the server side.
 
Thanks for the replies. Went to the Oracle forums, and yes, Oracle selects
are case sensitive UNLESS you use the UPPER() funtion (equivalent to UCASE in
VB). Once I used that, I could fetch my records okay. I'll look into using a
pass-through query, even though the datasets involved are pretty small.

I really appreciate the feedback!

Dennis
 
Dennis said:
Thanks for the replies. Went to the Oracle forums, and yes, Oracle
selects are case sensitive UNLESS you use the UPPER() funtion
(equivalent to UCASE in VB). Once I used that, I could fetch my
records okay. I'll look into using a pass-through query, even though
the datasets involved are pretty small.

I really appreciate the feedback!
Click to expand...

You're welcome, and thanks for posting back with your findings. That
should help the next person who comes along with a similar problem.
 
Back
Top