Data filtering with Base

classic Classic list List threaded Threaded
2 messages Options
Egbert Eissing Egbert Eissing
Reply | Threaded
Open this post in threaded view
|

Data filtering with Base

 
Hello,
I encounter a bigproblem by filtering data in a form by a date. Several days ofexperimenting did not produce any working result.
On of many filterstrings I experimented with is as follows:sFilter= chr(34) &sSearchfield & chr(34) & " = " & chr(39) &(sDescription) & chr(39) & " AND " & chr(34) &"Expense"& chr(34)&"."&chr(34)&"dateEntered"& chr(34) & " = " & chr(39) & aDate &chr(39) This produces anerror message: “incorrect property value”
Another filterstring which looks more promising goes like this:sFilter= chr(34) &sSearchfield & " = " & chr(39) & sDescription &chr(39) & " AND " & "Expense"&"."&"dateEntered " & ">= " & chr(39) & fDate & chr(39) & chr(34) The result shown ina message box looks as follows:"Description ='SalaryMidMonth' AND Expense.dateEntered >= '16/04/2016'"
After applying thisfilter, no data is shown on the form, instead the following errormessage:The data contentcould not be updated | Syntax error in SQL statement.
If I remove the date part of the filter, the filtering works fine. I believe theproblem is in the expression “ Expense.dateEntered “. Unfortunately I need tospecify the table name, because the query has two tables, both ofwhich have a “dateEntered” column. In all my previous datafiltering with dates, a table did not need to be specified, and italways worked without problem.
Here is yet anotherfilter formulation (same as before, but chr(34)) on both ends removed:sFilter= sSearchfield & " = " & chr(39) & sDescription &chr(39) & " AND " & "Expense"&"."&"dateEntered " & ">= " & chr(39) & fDate & chr(39) msgbox output:Description ='SalaryMidMonth' AND Expense.dateEntered >= '16/04/2016'This filter filterscorrectly by “Description”, but does not filter by date.
I have also beenexperimenting with different formats of the date specification, suchas “2016-04-16” as well as the “dim aDate as Newcom.sun.star.util.Date” format.
The column in theunderlying mySQL database table is of the “Date” type.
Can anybody help meby pointing out what is wrong with the above filter composition?Thanks very much inanticipationEgbert Eissing
--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Data filtering with Base