OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to make case insensitive form filter macro?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
vstarc16
General User
General User


Joined: 05 Oct 2006
Posts: 27
Location: Croatia

PostPosted: Mon Oct 15, 2007 5:25 am    Post subject: How to make case insensitive form filter macro? Reply with quote

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
View user's profile Send private message Send e-mail
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Mon Oct 15, 2007 11:45 am    Post subject: Reply with quote

you can change your data type to VARCHAR_IGNORECASE.
Back to top
View user's profile Send private message Visit poster's website
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sun Feb 24, 2008 7:12 am    Post subject: Reply with quote

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
View user's profile Send private message
windyweather
General User
General User


Joined: 14 May 2008
Posts: 22
Location: Coos Bay, Oregon

PostPosted: Mon Jun 02, 2008 5:46 pm    Post subject: Apparently UPPER only works with native databases? Reply with quote

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
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Tue Jun 03, 2008 12:12 am    Post subject: Reply with quote

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
View user's profile Send private message
Breandan
Newbie
Newbie


Joined: 18 Nov 2008
Posts: 3
Location: Spain & Ireland

PostPosted: Sun Nov 30, 2008 9:22 am    Post subject: Reply with quote

I found the same that in MySQL it is case insensitive when you use LIKE
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group