Show previous balance on report

[BrianLe]

In AC97, I need help getting a previous balance into a report. In the report I want to show the following

Beginning Ending
Date Inventory B1Belt B2Belt Adjustments Inventory


for each day between the start and end date.

I'm using form frmStackingTubeInventoryReport to select the start and end dates. It has a toggle button with event procedure

Private Sub Toggle10_Click()
DoCmd.OpenReport "Stacking Tube Inventory - Short Range", acViewPreview
End Sub

I'm using the following query qryStackingTubeInventory to get the B1Belt, B2Belt, and Adjustment values for the report.

SELECT tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.PileAdjustment,tblBeltScales.StackingTubeAdjustment
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) Between
   [Forms]![frmStackingTubeInventoryReport]![tbxStartDate] And
   ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate]);

I've found how to get the previous balance with the following query named "tblBeltScales Query".

SELECT Sum(nz([B1Belt],0)-nz([StackingTubeAdjustment],0)-nz([B2Belt],0)) AS Balance
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) < ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate]);

However, I can't figure out how to get the beginning balance into the report, since the report forms Record Source is qryInventoryStackingTube, and it doesn't have a control source for beginning inventory.

I've tried a text box for the beginning inventory with a control source

=[tblBeltScales Query]![Balance]

but I get the message "Enter Parameter Value tblBeltScales Query"

Any suggestions?

Thanks,

Brian

 

[BrianLe]

Please disregard. I've reposted on the Query forum.

Thanks,

Brian