Re: Re[2]: [GENERAL] case-insensitive like operator

Best way to handle this IMHO is to write a routine that uses Access
direct ODBC stuff to handle queries that must be case insensitive -
either that or use LCase(fieldname) & LCase(searchvalue) in your
Access queries, but this will be slower.

Here's my routine in VB for the former:

Public Function SQLPass(SQL As String, Optional DontLog As Variant)
Dim wrkODBC As Workspace
Dim conSearch As Connection
Dim qdfTemp As QueryDef
Dim connectstring As String, success As Byte, errornumber As Long

If IsMissing(DontLog) Then DontLog = False
DontLog = True
connectstring = "PUT YOUR CONNECT STRING HERE"
Set wrkODBC = CreateWorkspace("", "moray", "", dbUseODBC)
Set conSearch = wrkODBC.OpenConnection("Search", , ,
connectstring)

Set qdfTemp = conSearch.CreateQueryDef("")

With qdfTemp
.Prepare = dbQUnprepare
.SQL = SQL
success = 1
On Error GoTo SQLerror
.Execute

End With
If Not (DontLog) And (Nz(InStr(SQL, "UPDATE"), 0) > 0 Or
Nz(InStr(SQL, "CREATE"), 0) > 0 Or Nz(InStr(SQL, "INSERT"), 0) > 0 Or
Nz(InStr(SQL, "DELETE"), 0) > 0) Then
LogSQL SQL, success ' log if it is an update or delete or
create or insert query...
End If
conSearch.Close
wrkODBC.Close
Exit Function
SQLerror:
errornumber = Err.number
' Debug.Print errornumber
On Error GoTo 0

If errornumber = 3146 Then
success = 0
MsgBox ("ERROR trying to do " & SQL & " in server database.")
Resume Next
Else
success = 0
If Not (DontLog) = True Then LogSQL SQL, success
Err.Clear
Err.Raise (errornumber)
End If
End Function
----------------------------------------------------------------------
----------------
Moray(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
----- Original Message -----
From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: yura <yura(at)vpcit(dot)ru>
Cc: pgsql-general <pgsql-general(at)postgreSQL(dot)org>
Sent: Sunday, January 16, 2000 5:13 PM
Subject: Re: Re[2]: [GENERAL] case-insensitive like operator

On 2000-01-15, yura mentioned:

> Hello Adriaan,
>
> Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
>
> AJ> Postgres has a load of text search operators. The most powerful
is ~*
> AJ> whcih gives you a case insensitive regular expression search.
>
> AJ> Adriaan
>
> Thank you for reply, but i can't use ~* operator, because i don't
> create queries, MS Access generates queries and it creates queries
> with 'like' operator.

The way I read the SQL standard, LIKE matches are always
case-sensitive,
so Access would be in violation.

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

************