Using Date interval to filter records in table/query

[mariust]

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

[Villeroy]

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.

[mariust]

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

[Villeroy]

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.

[Villeroy]

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.