Report not working off query

[bailey11]

I have a query that picks up a range of records based on dates entered on a form. The query then summarizes similar records for the time frame.

I then wrote a report based off this query, however, when I go to run the report, it is asking me for an additional field "Wokdate" (the field the query uses to sum the hours). I think the report wants the field on it since it becomes the reports filter, but I can't add the field to the query, or I lose my sum ability and get individual records for each workdate.

How do I correct this problem?

Thanks, Bailey11

 

[dhookom]

What do you mean by "when I go to run the report"? Are you attempting to use the WHERE clause of the DoCmd.OpenReport method? If so, I don't think you can do this if you report's record source won't provide that level of detail.

You may be limited to using the form controls in your saved query or SQL string and not use the WHERE clause for the date range.

The other option that might work would be to NOT summarize in your report. Then create grouping in your report and hide the detail section so only totals display in group headers or footers.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Yes, I was using the Docmd.openreport.

Is there a way to write code to email a query? I can't seem to get it correct, but that would work also.

THanks, Bailey11

 

[dhookom]

I suggested a couple fixes to the report issue. Did you try them? If so, what did you do and what were your results?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

I have been trying to do it in the report and I can't get the results I need.

I am not sure what you mean but not using the "Where clause" because I have to have a specified daterange as they are payroll transactions.


Here is an example of the records....

Emp# JobNo Phase1 AccNo PayID Units WorkDate
123123 26000 980021 1 12.00 07/18/06
123123 26000 991202 10059 0 12.00 07/17/06
123123 26000 991202 10059 0 12.00 07/18/06
123123 26000 991202 0 7.00 07/19/06
123123 26000 991202 0 6.00 07/19/06
123123 26000 991200 0 4.00 07/20/06
123123 26000 991200 1 8.00 07/20/06
123123 26000 991200 1 12.00 07/21/06

I need the following results (with the sum of the units)
sorted by Emp#, JobNo, Phase1,AccNo,PayID.

So it would look like this
Emp# JobNo Phase1 AccNo PayID Units
123123 26000 980021 1 12.00
123123 26000 991202 10059 0 24.00
123123 26000 991202 0 13.00
123123 26000 991200 0 4.00
123123 26000 991200 1 20.00

Maybe I have been looking at it too long, but I can't get the report to do this. That's why if there is a way I can email the query, that would work.

Sorry for all the confusion, I just can't get there and it seems like it should be so simple.

Bailey11

 

[dhookom]

Have you tried setting your report's record source query to something like:
SELECT ....
FROM ...
WHERE WorkDate BETWEEN Forms!.. AND Forms!....
GROUP BY ....;
This would automatically limit the dates.

My other solution was to use your reoords with the date field and create sorting and grouping by
Emp#, JobNo, Phase1, AccNo, PayID

Set the PayID to display a group footer where you would bind the fields to text boxes and inclue a text box with a control source of:
=Sum([Units])
Hide the detail section and use the dates in the WHERE clause of the DoCmd.OpenReport



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Thank you. I think I got stuck in the trees and couldn't see the forest. I appreciate all your help and patience.