Storing numbers until next record

[DianeA]

I've got a report which is generated from a query. For each record in the query I calculate Qty * dollars. But there are records which will have 0 in the qty so I need to use the last known qty for the next records until the qty is no longer 0. The total dollars are summarized.

Not sure how to store the last known qty.

 

[jwrocko]

You can add a new table lastQuantity. Everytime an item is used, either update the quantity or add the item into it. Its difficult to know what needs to be done without any additional information though.

 

[Remou]

An alternative would be to use a function.

Option Compare Database
Option Explicit

Dim varLNo

Function LastNo()
If IsNull(varLNo) Then
    varLNo = Me.[Qty]
End If

If Me.Qty= 0 Then
    LastNo = varLNo
Else
    LastNo = Me.Qty
    varLNo = Me.Qty
End If
End Function

This can be used in a Textbox:

=LastNo()

 

[DianeA]

The function is working wonderfully.. thanks. Now... can I use that textbox in a calculation? I've named the textbox "Last" and used it to multiple with another field but I get #Name?

 

[DianeA]

sorry... got it working..

thanks again

 

[GingerR]

Hey Diane, I think Remou deserves a star!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Remou]