Generating Weekly Reports Using Query By Box

[genericjanedoe]

I am trying to produce a report using a Find query that sorts on the records modified during the last seven days.

I know that by using the formula: Database."Record Modification Date"='@Today()' I will get all records modified on that day, but I need a formula that will also allow me to search variably up to seven days earlier than the current date, and one month before the current date.

Does anyone have any idea what the formula would look like for this? Thanks.

 

[paulbent]

I don't advise using Query By Box, especially with dates. It's a bit buggy. Create a named find on the view:

On a form based on the table to be searched, in Browse, from the menu: Create | Named Find/Sort | New | View. Click in the date field and enter this as the condition:

>=@Today()-7

Hit Enter, type a name for the find and OK out.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[genericjanedoe]

Thanks for this, it works! The only problem is that if I create the find in View, I have to use the formula in a particular field. Afterwards I don't seem to be able to use this same field to do a different search.

So for instance, I can type in the find conditions for records modified in the last 7 days on the "record modified" field, but if I wanted to do a similar search for records modified in the last 30 days, I could not create a new field box with data from the record modified field, because the minute I tap the new formula in, it changes the data for all boxes using that field to the same formula!

I really don't want to use the find assistant in this particular form. Is there any way around this problem?

 

[paulbent]

Create a variable field, type numeric, named vAge say. Create one named find on the view with this formula, substituting your own table and field names:

If(Table.DateField >= @Today() - vAge)

Put the variable field on the form, enter whatever days age you wish and run the find.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk