Related Information Examples & Tutorials
Want new features? Check out the news on the latest version of Collect! available. Members can download new enhancements FREE! Interested in new features available but not currently on a Membership? Please contact us for pricing. Enhancements may be purchased separately.
“ I looked into many products before purchasing Collect!, but none had the features, ease, detailed instructions, or customer service of your company and product. ” Judy A.

How To Use Where Clauses With Dates

These are additional examples of WHERE clauses using date codes and arithmetic operators.

WHERE and @m

To print a list of all transactions recorded during the last month, set up a conditional WHERE clause in a loop of all transactions.

@tr WHERE (@tr.pd = @m .. @m+1-1)
@tr.pd @tr.de @tr.tu
@tr

This WHERE clause will print every transaction from the 1st of this month to the last day of the month. Let's look at each date specifier in turn.

@m represents the first day of the current month.

@m+1-1 represents the 1st day of this month, plus 1 month, minus one day. See How To Calculate Date Ranges.

tip.gif Any database field that holds a date can be used in an expression with arithmetic operators.

For information about the use of WHERE clauses, refer to How To Filter Data With The Where Clause.

For information about the loop used in this code, refer to How To Print A List of Records.

Where And Date Prompt

You can print a list of all transactions recorded during an arbitrary time period, and have the report system prompt you for a date range when your report is run. To do this, set up a conditional WHERE clause to prompt for the date.

@tr WHERE (@tr.pd = ?)
@tr.pd @tr.de @tr.tu
@tr

The question mark tells the system to prompt for a range of dates when the report is run. This particular example will only work if you are on the Transaction form.

Top of page.

Date Range With Prompt

The following example shows the use of operators in a WHERE clause using @tsr.fr and @tsr.to. These two dates: [ Transaction Summary Report.From ] and [ Transaction Summary Report.To ], can be set by prompting for a Debtor Listing Date Range.

To prompt for the Listed Date at run time:

@de WHERE (@de.li = ?)
@de

You will be prompted to enter a From and a To date when the report is run. These dates are actually stored in the database.

To print all debtors within this range, the following code is used:

@de WHERE (@de.li = @tsr.fr .. @tsr.to)
@de.li @de.na
@de

tip.gif The values for tsr.fr and tsr.to stay set until they are changed by another prompt for a date range. This means that you can reference these dates in your code as above. The result will be a Date Range that is identical to the range you set earlier in the prompt.

Top of page.

See Also

- Report Sample to view sample reports and letters
- Report Topics Index for a list of all report and letter topics
- How To Filter Data With The Where Clause for detailed information
about WHERE clauses

Top of page.

Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.

Please click this link to send us your comments: helpinfo@collect.org

RMA RMA Canada ACA Member