Used in the Custom Report Designer to locate the result from a SQL summary query.  Can be used to count or summarize a variety of statistics such as counts of work orders of certain types, equipment counts, etc.

USING:

Enter a valid SQL Query (using Access syntax) and click Test ==>.  The first result (i.e. first Row,Column of the result set) of your query will be returned.  Click OK to locate the value into the report.  The #SD# and #ED# correspond to the Start Date and End Date that is set for the report when the user runs the report.  The current report dates are shown at the bottom of the locate screen. 

Use the Table combobox (on the right side of the form) to see the fields in each table. 

Locates a textbox with the r.sqlresult formula in the datafield.

 

EXAMPLES:

1. Count of Work Orders that are more than 45 days overdue.  Open WOs have a RecType of 1.  Date() returns the current date.

Select Count(*) FROM WO WHERE DueDate<= Date()-45 AND RECTYPE=1

2. Count of Work Orders that are due in the next week with a Work Order Priority of greater than 2:

Select Count(*) FROM WO WHERE DueDate >= Date() and DueDate<= Date()+7 AND RECTYPE=1 AND [WOPriority ] >2

3. Count of Equipment of Type PUMP:

Select Count(*) FROM  Equipment WHERE  [Type] = 'PUMP'  

NOTE:  Notice PUMP is in single quotes, double quotes CANNOT be used.

4. Total cost of closed work orders in the Report Date Range.

 SELECT SUM([TotalCost]) FROM WO WHERE [DateComplete]>= #SD# and [DateComplete] <= #ED#

 

Download Report Template SQL_TRAINING.RPX for these and more examples: preview