Multiple columns of data on the same report based on different dateparameters

[Brett]

Thanks in advance for your responses.

I am creating one report with several columns that pull data from 2
different tables and are based on different date parameters. I am
pulling the data from QuickBooks via ODBC and the date format data
returned is in the format m/dd/yyyy or mm/dd/yyyy.

One column will show the sum of all line items (SalesOrderLine.Amount)
based on all sales orders with the ShipDate that contains the current
month.

Another column will show the sum for all sales orders with ShipDate
for the beginning of the following month and beyond.

Another column will show the sum of all line items
(InvoiceLine.Amount) for all invoices with the TxnDate for the current
month.

Another column for all invoices with the TxnDate for same month last
year as the current month.

Another column for all invoices with the TxnDate from the beginning of
last year until the same day last year as the current day when the
report is ran.


I have tried using the Date() function as both a parameter or as part
of a build expression, but I get an ODBC conversion error. I have also
tried using the Month() function, but it gives me 12 rows of data, the
sum for each month when I only want the current month, Month = 6 for
June, for example.

A few questions: should I be using a date prompt (From and To) for the
current month in the report? Should I be using any date parameters in
the queries or should that be done in the report itself and how is
that done?

Any resources you can provide where I can also learn how to do this,
which seems pretty complex, would be appreciated.

Brett

 

[S.Clark]

I would create a form to gather the user desired criteria. Then call the
report to specify the month.

Using the OpenReport method/action and set the Where parameter to filter the
data

"Month([FieldName])=" & cboMonth

Where the combobox cboMonth is bound to values 1-12.