[Solved] Filtering by date, problem with input via DateField

[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

[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"

[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

[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?

[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

[DrewJensen]

Does the date control have a value set for "Default date"?

[vstarc16]

No, it does not have.

[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.

[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

[DrewJensen]

Wonderful, and thanks for adding the SOLVED tag..

[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?)

[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!