Multple Queries One Report New Twist

[roxannep]

Having reviewed what's already been posted, I have not found an adequate solution to my delimma, so here goes:

I have a Transactions table, from which I'm querying records for a sales report. The necessary fields are: PaidDate, AmtPaid, RefundDate, Refund, Cost1, Cost2

When the Sales Report is needed, it pulls up a form asking for the date range for PaidDate, then pulls the sales transaction information completed during that time. Fine. However, I also need it to pull refunds handled during that time, but the sales portion of those transactions may have been completed months ago. The summary report needs to have totaled all sales that were completed during the month, but back out any refunds completed during that month, regardless of when the original sale took place (and the original sales data should not be included if it occurred outside the date range parameters), then it needs to calculate net sales based on: sales less cost of goods less refunds equals net sales for just the date range indicated.

Example:

For Dates 4/1/01-4/30/01

2 Sales transactions:

AmtPaid Sum: $10

1 Refund

Refunds Sum: $2 (original sale closed 2/5/01 for $15, but I don't want that amount included in the sales info on this April report)

Net Sales: $8

I have tried attacking it with two queries so as not to include the "sales" amounts in my totals when it's the refund information I need. I don't know how to combine the data the queries pull into one report or a report with a subreport, and then be able to have a Net Sales sum pulling from the main and subreport for a final total.

Beginning to confuse myself! Any help greatly appreciated (and obviously needed) ....

 

[PaulF]

Something along this line might work for you. Use the query for the report without providing any criteria. Add an additional textbox (unbound) for the net value of the record (sales and refunds) and an unbound textbox for the total. Using the Report's Open event, capture the Start and End dates for the report and then set a filter based on those values. This example uses a textbox named txtNet as the result of the calculations for the Sales and Refund data and a textbox named txtTotal for the total of all dates. The control source for txtNet is a compound IIf statement which checks the date of sales and refunds calculates accordingly

=IIf([PaidDate] Between [dtStartDate] And [dtEndDate],[AmtPaid]+IIf([RefundDate] Between [dtStartDate] And [dtEndDate],[Refund]*-1,0),[Refund]*-1)


Option Compare Database
Option Explicit
Public dtStartDate As Date
Public dtEndDate As Date

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
txtTotal = Val(Nz(txtTotal, "0&quot) + Val(Nz(txtNet, "0&quot)
End Sub

Private Sub Report_Open(Cancel As Integer)
dtStartDate = InputBox("Enter Start Date (01/01/01):&quot
dtEndDate = InputBox("Enter Start Date (01/01/01):&quot

Me.Filter = "[PaidDate] Between #" & dtStartDate & "# And #" & dtEndDate & "# or [RefundDate] Between #" & dtStartDate & "# And #" & dtEndDate & "#"
Me.FilterOn = True

End Sub


HTH

PaulF