(Without having to create global fields in another incidence of the table) ... I seem to remember that in Access,one would run a query with calculated fields for 'start' and 'end' date (which the user would input).
You need global field for user input or allow the User to type directly into the service date itself; how else can you specify the value for the range? You can use globals which are available in ANY table (even if not related). I'm unsure where you get that they must be 'in another instance of the table.'
If your range for the service date is consistent, for instance if it is always the prior month then you can script it without User input at all. So please tell us what kind of ranges you wish and if you want user input. I can make several suggestions but it would depend upon your specific needs.
Thank you for your response.
I am trying to teach myself with the aid of a manual. The only reference I found for date ranges, was to set up a global field, which they show to set up using a related table. I have probably misunderstood.
I would ideally like to do both. Script so that the report can show what services are due for the next fortnight and also set up a 'user input' so that the users can run a report for dates of their choice.
You CAN have the best of both worlds. I would suggest using globals in case the User wants to enter their own date range. But you can prefill the globals with fortnight search (today through today + 14). The User can change the dates as they wish since it will be provided in Custom Dialog.
I've provided a sample file DateRange.fp7
By the way, if you delete the starting date, you will get all records prior to (and including) the ending date. If you delete the ending date, you will get all records from the starting date forward.
Date ranges (and most ranges) do NOT need start and ends.
...7/15/2010 will find all records prior to (and including) 7/15/2010.
6/25/2010...will find all records after (and including) 6/25/2010.
You can even search dates for ...Wed and it would find dates which fall on Sun, Mon, Tues and Wed.
Ranges using ... or .. don't need beginnings and ends to work properly. This is why I don't protect from empty fields in my custom dialog.
Thanks a million, I was ready to tear my hair out.