Page 1 of 1
[Solved] Filtering by date, problem with input via DateField
Posted: Sat Apr 12, 2008 1:17 pm
by vstarc16
Hello to everybody.
What I need is that end user is able to filter data by selecting drop down calendar from Date field. Trying to find a solution, with a help from OO Forum i did the following code and it works well
Code: Select all
Sub filter_by_date
Dim oFormCtl As object
Dim strFilter As string
Dim oFilter As Object
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("DateField")
if oFilter.Text <> "" then
oFormCtl.Filter="date = {D '2007-10-10'}"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub
Then I did the following change:
Code: Select all
strFilter="date = {D '" & oFilter.Text & "'}"
oFormCtl.Filter=strFilter
but I am getting the following error message:
SQL Status: 37000
Error code: -16
Wrong data type: java.lang.IllegalArgumentException in statement [SELECT * FROM "Table1" WHERE "date" = '2007.10.10' ]
when the form is loading, and afterwards nothing is happening no matter what date is selected in Datefield.
It becomes really frustrating and I would be very grateful for help.
Vanja
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 1:34 pm
by DrewJensen
Open the form form in EDIT mode.
Select the date control ( or any control really ), right click and select Form.
In the Data tab look at your content. It should be the SQL command:
SELECT * FROM "Table1" WHERE "date" = '2007.10.10'
Try removing the WHERE clause
SELECT * FROM "Table1"
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 2:17 pm
by vstarc16
Hello Drew,
I did as you advised, but without effect. No matter whether Content type is a table (Table1) or SQL command (SELECT * FROM ''Table1'') Filter field is allways set to the last value entered via DateField but it is not executed. Instead I am getting error message
SQL Status: 37000
Error code: -16
Wrong data type: java.lang.IllegalArgumentException in statement [SELECT * FROM "Pregledi" WHERE "datum" = 'Value I entered' ]
I even tried to switch off Analyze SQL command, but as expected, after the change in DateField, data are loaded without being filtered.
I forgot to mention I am running OO 2.4 on Win XP
Vanja
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 2:20 pm
by DrewJensen
Ok - by chance have you set the 'when loading' event for the dataform control to call a Basic procedure, and if so what happens in that procedure?
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 2:34 pm
by vstarc16
No, there is nothing selected on When loading event of the form. Code I mentioned before is linked to Text modified event of the DateField control.
Vanja
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 2:39 pm
by DrewJensen
Does the date control have a value set for "Default date"?
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 3:04 pm
by vstarc16
No, it does not have.
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 3:09 pm
by DrewJensen
alright well, let me back up then..
First - that error message form the first post here about the SQL statement:
SELECT * FROM "Table1" WHERE "date" = '2007.10.10'
The error here is that the date is formatted in correctly it needs to be
SELECT * FROM "Table1" WHERE "date" = '2007-10-10'
Because, it is a default translation of a string to date HSQLdb requires it be ISO compliant, hence the dashes.
Second - another question. When was the last time you could open the form and have it display data? Was it just prior to assigning the event to the date control, or sometime after wards? What I am getting at is, can it be undone and then find the proper way to make the change.
Re: Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 3:17 pm
by vstarc16
Drew,
simultaneusly with the last post I have realised that date format was causing the error message, and now the filter is working correctly. I wish to thank you for the all effort in helping with this issue, as you did many times before.
Vanja
Re: SOLVED:Filtering by date - problems with input via DateField
Posted: Sat Apr 12, 2008 3:23 pm
by DrewJensen
Wonderful, and thanks for adding the SOLVED tag..
Filtering by date, problem with input via DateField
Posted: Sun Jun 22, 2008 11:50 am
by ugm6hr
Sorry to jump in here - I'll start a new thread if no one is still watching this one...
I'm trying to do the same thing (in Base 3.0 beta / Ubuntu 8.04), but can't get this macro to work on my form.
The form is based on a table, created with the Wizard. I then added a date control (DateField) with dropdown in design mode, using the YYYY-MM-DD format. The date column in the table is "Date" rather than "date", but the macro is otherwise identical. Set this macro to run on "Text modified" in DateField.
When I open the form, the table data loads OK, but when I change the date in Datefield, I get the following error:
The data content could not be updated.
Syntax error in SQL expression
The form works fine without the macro attached. I have also used an almost identical macro to search using a text field, which works just fine.
The macro - in case there's a typo (but it was just copied and pasted):
Code: Select all
Sub datesearch2
Dim oFormCtl As object
Dim strFilter As string
Dim oFilter As Object
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("DateField")
if oFilter.Text <> "" then
strFilter="Date = {D '" & oFilter.Text & "'}"
oFormCtl.Filter=strFilter
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub
Where am I going wrong?
Ultimately, I want 2 similar fields with a >= and <= (i.e. from and to dates) to filter the records with. But I can't get the syntax right.
EDIT: I discovered that the macro requires the column to be entered as `Date` rather than just Date:
Code: Select all
strFilter="`Date` = {D '" & oFilter.Text & "'}"
Now - can anyone tell me how to have 2 separate DateFields (DateFieldFrom and DateFieldTo) in a macro? Hopefully a bit of experimentation will solve this for me. Obviously, writing a separate macro for each merely applies one or other filter (presumably due to the oForm.Ctl.Reload?)
Filtering by date, problem with input via DateField
Posted: Sun Jun 22, 2008 2:41 pm
by ugm6hr
OK. worked some of it out myself:
Code: Select all
Sub datesearch3
Dim oFormCtl As object
Dim strFilter As string
Dim oFilterA As Object
Dim oFilterB As Object
oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilterB = oFormCtl.getByName("DateFieldTo")
oFilterA = oFormCtl.getByName("DateFieldFrom")
if oFilterA.Text <> "" then
if oFilterB.Text <> "" then
strFilter="`Date` >= " + "{D '" & oFilterA.Text & "'}" + " AND `Date` <= " + "{D '" & oFilterB.Text & "'}"
oFormCtl.Filter=strFilter
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if
else
oFormCtl.ApplyFilter = False
end if
oFormCtl.Reload
End Sub
Just connect this macro to a pushbutton (labelled Search) rather than directly to the DateFields.
Works great, as long as you make sure to manually select dates for both From and To. Only 1 problem left - the macro runs with values for DateFields that do not match my default values if you leave the fields blank. In fact, it appears to run with the 1st values I set when testing it. Even changing the default values in DateFields makes no difference.
I need some way to clear the date filter within the macro, or when loading the form. Ideally, I would have a Search toggle - that toggles between search and no filter. A little more research required.... All help gratefully received!