On Aug 20, 11:49*am, rebeca wrote:
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]);- Hide quoted text -

- Show quoted text -

Update. Here is part of the resolution to my issue. I wrote an IIF
statement and attached it to the unbound text box that displays the
beginning balance.

=IIf([Beginning_AR_Date] Between Forms!datepicker!startdate And
Forms!
datepicker!enddate,[amount],IIf([Beginning_AR_Date]Forms!datepicker!
startdate,"0.00",IIf([Beginning_AR_Date]Forms!datepicker!enddate,
[RunningSum],"error")))

My only question now is for the last IIF statement. How do I code it
to show the running balance for the last date of the previous month?
I
ran the report for August. The "beginning balance" is showing the
balance on August 3 which was the first date that entries were made
for August.


Thanks!