On Aug 18, 4:01*pm, KARL DEWEY
wrote:
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has. *If I had this kind on information I might be able to suggest
a possible solution. *Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
*If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
--
Build a little, test a little.



"rebeca" wrote:
I have a query calculates a running AR balance for multiple
physicians. Here is what I need to accomplish on a report.

Each physician has a record in the provider table that reflects their
initial Beginning AR balance and the date of the AR Balance. For
example, one physician has a Beginning AR date of 07/01/09. This
information is included in the query.

1.) * * * *How can I get the balance to display only for 07/01/09-07/31/09?
Any date/amount prior to the Beginning Date should be empty.
2.) * * * *How do I then take the Ending AR Balance on 07/31/09 and use that
as the Beginning Balance as of 08/01/09, etc. without writing it to a
table (if possible)- Hide quoted text -

- Show quoted text -

going to try to explain this a different way.

When the report is opened, I need to compare the selected start date
for the report to the date associated to the beginning AR
(beginning_AR_date). If the dates are the same month and year, I want
this amount to display in the [Amount] field. If they are not the
same, I need the runningsum for the start date of the report
displayed.

Fields:
([forms]![datepicker]![start date])
[beginning_ar_date]
[amount]
[runningsum]

Here is my SQL information:

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SN AR Log Query - By Physician].Amount,
Sum([SN AR Log Query - By Physician].total1)+[SN AR Log Query - By
Physician].amount AS RunningSum, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date])
AS ReportDate

FROM [SN AR Log Query - By Physician] INNER JOIN [SNProvider Table] ON
[SN AR Log Query - By Physician].MDID = [SNProvider Table].MDID

GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date]);