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.
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.
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
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.
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
| 
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