| View previous topic :: View next topic |
| Author |
Message |
vstarc16
General User
Joined: 05 Oct 2006
Posts: 27
Location: Croatia
|
 Posted: Mon Oct 15, 2007 5:25 am Post subject: How to make case insensitive form filter macro? |
 |
|
On the forum I found macro that filters records for the form and slightly modified it.
Here is the script:
| Code: | Sub Search
dim oFilter as object
dim oFormCtl as object
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("TextBox")
if oFilter.Text <> "" then
oFormCtl.Filter = "Name LIKE " + "'%"+oFilter.Text+"%'"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub |
It works well, but it is case sensitive. It would be better for the application purpose if it would be case insensitive and I would appreciate if somebody could suggest modification.
Vanja |
|
| Back to top |
|
 |
QuazzieEvil
Super User
Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX
|
| Posted: Mon Oct 15, 2007 11:45 am Post subject: |
|
|
| you can change your data type to VARCHAR_IGNORECASE. |
|
| Back to top |
|
|
Voobase
OOo Advocate
Joined: 21 Nov 2007
Posts: 400
Location: Australia
|
| Posted: Sun Feb 24, 2008 7:12 am Post subject: |
|
|
An alternate way to make case insensitive is to simply put UPPER before the field_name on both sides of "LIKE" in the SQL statement. As both the field_name data and text box search data are capitilized, the search is no longer case sensetive. Similarly you could use LOWER.
| Code: | Sub Search
dim oFilter as object
dim oFormCtl as object
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("TextBox")
if oFilter.Text <> "" then
oFormCtl.Filter = " UPPER(Name) LIKE " + "UPPER('%"+oFilter.Text+"%')"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub |
|
|
| Back to top |
|
|
windyweather
General User
Joined: 14 May 2008
Posts: 22
Location: Coos Bay, Oregon
|
| Posted: Mon Jun 02, 2008 5:46 pm Post subject: Apparently UPPER only works with native databases? |
|
|
I get an error when I use UPPER() with a filter and MySQL.
The error I get is:
SQL Status: 42000
Error code: 1305
[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]FUNCTION dvd_video.UPPER does not exist
The SQL command leading to this error is:
SELECT * FROM `dvd-collection` WHERE UPPER ( `MovieTitle` ) LIKE UPPER ( '%lethal%' )
I'm going to do some research about how to do a case insensitive query using MySQL.
It seems clear how the filter works now. It's appended to a SELECT statement. That is pretty interesting.
I'm finding that MySQL actually is doing a case INSENSITIVE search with the LIKE statement.
| Code: | Sub SearchByTitle
dim oFilter as object
dim oFormCtl as object
REM oFormCtl = ThisComponent.Drawpage.Forms.getByName("TitleSearch")
oFormCtl = ThisComponent.DrawPage.Forms.GetByIndex(0)
oFilter = oFormCtl.getByName("txtTitle")
if oFilter.Text <> "" then
rem oFormCtl.Filter = " UPPER(MovieTitle) LIKE " + "UPPER('%"+oFilter.Text+"%')"
oFormCtl.Filter = " MovieTitle LIKE " + "'%"+oFilter.Text+"%'"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub
|
- windy |
|
| Back to top |
|
|
Voobase
OOo Advocate
Joined: 21 Nov 2007
Posts: 400
Location: Australia
|
| Posted: Tue Jun 03, 2008 12:12 am Post subject: |
|
|
Hi there,
I've only tried this out with the default HSQL backend and havn't used MySQL as a backend. I guess I'm not much help then, other to say that it is usually the combination of single and double quotes around things that can create some fun with fault finding.
Just cheching. Does the macro work correctly without "upper" (and the brackets)
Just something to try, but if you make MovieTitle a string variable, this is what I had to do for it to work...(incorporate this into your code)
| Code: | dim varMovieTitle as string
varMovieTitle = "MovieTitle"
tmp.filter = "UPPER(""" & varMovieTitle & """) LIKE " + "UPPER('%"+Search_Term+"%')" |
I actually got the "Upper" idea from a SQL website (can't remember which one right now). If you find a solution please post back as it would be interesting to know.
| Quote: | | I'm finding that MySQL actually is doing a case INSENSITIVE search with the LIKE statement. |
Oh ok then. I guess you don't need the "upper" then as long as you are using "Like"in the expression. Guess still nice to know if "upper" works when using = etc.
Cheers
Voo |
|
| Back to top |
|
|
Breandan
Newbie
Joined: 18 Nov 2008
Posts: 3
Location: Spain & Ireland
|
| Posted: Sun Nov 30, 2008 9:22 am Post subject: |
|
|
| I found the same that in MySQL it is case insensitive when you use LIKE |
|
| Back to top |
|
|
|
