Date Filters

[juneau730]

In the dBase we use at work, that I have been working on for ever and a day, we have a lot of date filters built into queries. I was wondering if there is a simpler way to standardize them, so they all look similar, but perform the functions needed.

They all need to be a greater than/equal based off the current date. In some I have <=Date()-30 (which work for that this filter needs) In others I have Between (Date()-179) And (Date()-90) to give me any date greater than 90 days, but less than 179 from current.

These work, but just wondering if there is a better way to do them?

 

[xenou]

It is generally possible to re-write such a query so that it takes a range you can set at runtime (simplest case: user enters a range such as "startDate" and "endDate"). Generally that would be called a parameterized query.

 

[Micron]

My main concern with a parameter query would be that if either one of the inputs was invalid, the query will still run. Will the fact that no records are returned be interpreted as there are no records that meet the criteria or will the user somehow know that one was not a valid date (or even that one date was on the wrong side of the operator thus could never be true)?

Many ways you could do this, including a combo of operators and 1 or more textboxes for inputs; e.g. if less than is chosen from the combo, show one control. If Between is shown, show 2 different textboxes (in either case they are formatted to display the built in calendar control so that actual dates are guaranteed). In any case, you can then apply validation such as start date cannot be later than end date. With this approach there is no need to be concerned with x number of days before or after today's date. However, if that is important your design can work with user selections and a textbox (or spinner) for the number of days.