[IowaGuy]

I have used Crystal Reports 8.5 in the past. One of features I liked is when a report was ran, it prompted the used to specify a date range.
Can Access 2003 do that? If so, can you point me into the right direction on how to make it happen.

[ScottGem]

Yes Access can easily do that. There are different ways to do it, but the method I use is to create a form to kick off the report. On the (unbound) form, I place two text controls named txtStart and txtEnd. Also on the form is a button to run the report. The report itself is bound to a query. In the query the criteria for the date column is set to:

BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd

(use the correct name of the form). When you press the button to run the report, the query will be filtered by the range of dates.

[IowaGuy]

Scott, you are everywhere! Mercy.

Can you provide a link for this?
If I am unable to do it from the link, I see that you take calls. I'd be willing to call.

[ScottGem]

I'm not sure what you need a link for. I think the instructions I gave were pretty clear. I happened to have a sample of this so I'm attaching it. If you still need more help, let me know.

[IowaGuy]

Wow Scott.
It totally clicked. I got several reports functional with this method. Thanks!

Another question. Now, I am more of a graphics guy than a programmer. I took the basics in college, but nothing advanced. I have red some SQL books and I feel I understand the concepts until the advanced stuff. Ok..

In my report, I have a sum of 3 columns grouped by date. This is what it shows.
The total for each date. Perfect so far.

PrintDate Apple EverythingElse Total
---------- ------ ----------------- ------
11/4/2007... 100
11/5/2007... 552

I want it to do more. I want to know how many of the total were for Apple and how many were EverythingElse.

Apple has a CustomerID of 1
Grapes = 2
and so on.

In the report, I assume it's the report, maybe it's the VB code view.
I created a text box for Apple (txt_Apple)and EverythingElse (txt_EverythingElse) and Total (txt_Total)

I am trying to write something that stuffs the sum of txt_Total if customerid = 1
for txt_Apple.
Also to stuff the sum of txt_Total if customerid <>1 for txtEverythingelse.

Am I going in the right direction and if so, what would be the correct syntax?

[ScottGem]

I'm not quite clear on your table structure here. Does it look like this:

PrintDate
CustomerID
Amount

If so, there are a few ways to do this. Probably a Union query would be easiest. First you create two group by queries. Both Grouping by Date and summing the amount. In one query you add a WHERE column to filter for CustomerDI =1 In the other CustomerID <> 1. Then Union those tow like this:

SELECT PrintDate, SumOfAmount AS AppleTotal, 0 AS NonAppleTotal
FROM qryApples

UNION SELECT PrintDate, o AS AppleTotal, SumOfAmount AS NonAppleTotal
FROM qryOthers;

Finally, use the Union quey as the source of another query where you add the two total columns to get the overall total.

[IowaGuy]

Hi Scott,

My job table looks like this:

JobID (pk)
CustomerID
PrintDate
Documents
Addl_Pages
Wasted_Paper

There are more columns, but I didn't want make this more confusing.

In my report. I want the sum of (Documents,Addl_Pages,Wasted_Paper)
This will show the total amount of paper was used to print.

I also want to show in the report how much of the paper was used for Apex(Apple) and how much was for everyone else.

I wrote a SQL statement, in what I tried to base on your outline.

SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
FROM Job
WHERE CustomerID="1"
GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
ORDER BY job.printdate

UNION

SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Other
FROM Job
WHERE CustomerID <>"1"
GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
ORDER BY job.printdate


It pulls back data, but I think there needs to be a way to differentiate between the 3 results I need.

[IowaGuy]

Another thought, and I am not sure if its possible or not, but in the report properties, where the Record Source is at, is it possible to look pull more than 1 query?

[ScottGem]

Ok, this is why table structure was a key. You really have two thing going on here and you are trying to do the two things at the same time which isn't going to work well. My question to you is do you have show the breakdown on a daily basis. If so, I think there is a different way to go.

qryApex
SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
FROM Job
WHERE CustomerID="1"
GROUP BY Job.PrintDate
ORDER BY job.printdate

qryOthers
SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Other
FROM Job
WHERE CustomerID <>"1"
GROUP BY Job.PrintDate
ORDER BY job.printdate

qryDailySummary
SELECT qryApex.PrintDate, Total_Apex, Total_Others, Total_Apex+Total_others As Daily_Total
INNER JOIN ON qryApex.PrintDate = qryOthers.PrintDate
FROM qryApex, qryOthers
ORDER BY PrintDate

Then use qryDailySummary as the Recordsource of your report.

Disclaimer: The SQL is off the top of my head and untested, but it should give you an idea, I suggest using the query builder to build each query.

And no, you can't have more than one object as a Recordsource.

[IowaGuy]

Nice!

Thanks!