Using Date interval to filter records in table/query

Discuss the database features

Using Date interval to filter records in table/query

Postby mariust » Sun Feb 14, 2010 1:00 pm

Hi all,
this is the situation:
Tables: Customers, Bills (fields BillID, CustomerID, DateFrom, DateTo, BillingDate, Paydate), Hourlog (fields LineID, CustomerID, Text, Date, Hours, Rate)
Forms: Customers with Hourlog subform, used to record hours worked for the customer (works fine)

When I want to make the bill, I will choose the customer and specify the period to select from (Using Fields DateFrom and DateTo). This can be specifed in a form. Now I am encountering trouble, How can I use the specified form or tabel values in a query? I MS Access I would have used the expression builder to get the numbers from the table or form, but in OO I can't find any tool similar to the expression builder. When I am setting up a query to get the data, I have tried using BETWEEN in the criteria, but it doesn't allow me to use fields in the function.

Can anyone give me a hand in this, please?

Marius
XP/OOo3.2 OS X 10.5.8 /OOo3.2
mariust
 
Posts: 2
Joined: Sun Feb 14, 2010 12:35 pm

Re: Using Date interval to filter records in table/query

Postby Villeroy » Sun Feb 14, 2010 7:41 pm

Before someone comes up with some macro, I upload another example database with one form which is supposed to reflect your current form (more or less).
In the main form on the left you select a "Category" (only one named field) and the subform shows all related entries from table "List".
I bound the subform to an SQL statement (see form properties) to enforce archeological sort order (latest dates on top). This may help to find time spans easier.

Set the focus on the subform. There are 3 built-in methods to add user defined filters while working with a form.
1) The "magic stick" icon filters by the value in the currently selected field.
2) The form based filter. You push a button on toolbar "Form Navigation" and enter criteria similar to the query designer. This way I applied a filter where "Date" BETWEEN #1/1/08# and #15/5/08#.
3) Hit the last button on toolbar "Form Navigation" and get another grid view with a toolbar. The toolbar includes a button to call a filter dialog very similar to the standard filter in a spreadsheet. I have no idea why this filter option is limited to the extra view.
Attachments
mixed_filter.odb
(323.31 KiB) Downloaded 306 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28153
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Date interval to filter records in table/query

Postby mariust » Mon Feb 15, 2010 10:15 am

Thanks for your reply, Villeroy. Unfortunately, it didn't hit the target directly. I have attached my database, so its hopefully clearer to see what I am aiming for. I manage to link fields in the main form to fields in the subform, but you can only select similar records and not an argument, am I right? Initially I was thinking about using the query to set a relation between FromDate and Date, ToDate and Date and then use MIN as criteria on one and MAX as criteria on the other. Still haven't got that to work.

I have seen from your earlier posts you don't fancy the macros, but can you see any way around that, or is that the way to go? I am blank in that subject and can't see the trees in the forest, probably.

Marius
Attachments
BillingDatabase.odb
(11.23 KiB) Downloaded 166 times
XP/OOo3.2 OS X 10.5.8 /OOo3.2
mariust
 
Posts: 2
Joined: Sun Feb 14, 2010 12:35 pm

Re: Using Date interval to filter records in table/query

Postby Villeroy » Mon Feb 15, 2010 5:01 pm

Meanwhile I noticed that too many users can not even handle those parts of the given tool set that use to work out of the box. This is why I started with the 3 ways to apply filters. Macros hardly ever provide any sustainable solution. It seems as if nobody is able to adapt the existing code snippets.
Have a look at this recent topic while I have a look at your database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28153
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Date interval to filter records in table/query

Postby Villeroy » Mon Feb 15, 2010 8:24 pm

Look at this one. It's a bit chaotic. May be you don't need all the 4 forms in one.
A filter form with 2 dependent subforms and a sub-subform.
Enter filter criteria and refresh.
Attachments
BillingDatabase2.odb
(20.89 KiB) Downloaded 1380 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28153
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests