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

Thanks,
Brett

 

[KARL DEWEY]

You need subreports. The first three in one report and the last two in their
own subreport.
But first it sounds like you need to do some testing.You need to test if Access will accept these as valid dates.
Build a select query for the first like this --
SELECT [YourDateField], Format([YourDateField], "mm") AS Month_of_date,
Format([YourDateField], "mm") AS Year_of_date
FROM YourLinkedTable;

Do the same for all linked date fields.

Post the table names for those fields you indicated --
SalesOrderLine.Amount
? ShipDate
InvoiceLine.Amount
? TxnDate

 

[Brett]

SalesOrderLine.ShipDate

InvoiceLine.TxnDate

 

[KARL DEWEY]

Did you test those dates?

 

[Brett]

Sorry, Access doesn't let me save the select statement you have
posted.

 

[Brett]

The statement:

SELECT InvoiceLine.TxnDate, Format([TxnDate],"mm") AS Month_of_date,
Format([TxnDate],"yyyy") AS Year_of_date
FROM InvoiceLine;

produced the following examples:

TxnDate Month_of_date Year_of_date
9/9/2005 09 2005

TxnDate Month_of_date Year_of_date
10/18/2005 10 2005

TxnDate Month_of_date Year_of_date
12/11/2007 12 2007

I think I know where you are going with this....what a great help so
far!

 

[Brett]

TxnDate Month_of_date Year_of_date
6/11/2008 06 2008

So now how do I create a parameter to show only the invoices for the
month of June 2008, for example, based on the "06" returned in the 2nd
column, and 2008 in the 3rd column? Would this be a parameter in the
query or another query based on the query we just did? How do we link
it to the month and year of the current date so that I can do a
formula?

Thanks.

 

[Brett]

Ok, I think I've got it now:

By using in conjunction with Format(Date(),"mm") AS Today, I can
filter out just the data I need.

 

[Brett]

Now if I use Format(Date(),"mm") AS Today, how do I link that to the
"06" returned above? Do I do this in another query or in the final
report?

 

[Brett]

Okay, so now I've created a Make Table query:

SELECT Format(Date(),"mm") AS [Month], Format(Date(),"yyyy") AS [Year]
INTO DateToday;

and created a inner join based on Month_of_date and
Year_of_date...where the month and year are equal (for current month
results). Did I do this correctly? Will I then be able to create a
macro that will make the table first and then the report will show the
results? Is this the best way to do this?

Thanks,
Brett