populating a field with previous record data and condition/s

[lechu.555]

Thanks for helping:

In a select-query with three fields DateField, DateField2, and Balance; when a record has 'Balance' empty (null or blank) I need to populate DateField2 with last DateField record for which Balance was not empty. For instance:


DateField----------DateField2------------Balance
40142----------------40142-------------------10,000
40143----------------40142-------------------
40144----------------40142-------------------

Please, kindly advise…
lechu.555

 

[RuralGuy]

The only way I know of to do this is with a RecordSet and walking through the table. Will this be needed repeatedly or is it a one shot deal?

 

[lechu.555]

Thank you for replying;

repeatedly; a few times per month. I am checking a projection to the actual date; there are days I don't trade, so I need to picked up previous record; so I create an 'intermediate' query to do a DVlookUp on that last trading day. At the moment, I am doing quick and dirty with this: D3: IIf([DateField]<Format(Date(),"0") And IsNull([Equity]),DMax("[DateField2]","[qry_PnL_Analysis2]","[DateField]<" & [DateField] & "And [Dummi]=1"),[DateField]); the dummi is best to 1 if trade, 0 iif IsNull...

Recordset approach I think it's out of my depth; is like writing a function or code in VBE? ... If it is and easy to copy/paste/edit for this purpose I'd definitely welcome it... b/c I think my approach is not so robust and eventually will slow process, etc.

Thank you for your response RG.
lechu.555

 

[RuralGuy]

I don't think I completely understand what you are currently doing. Is DateFied2 also empty when the Balance field is empty?

 

[spaddhu]

Please refer to this thread, and see if this works out...

http://www.access-programmers.co.uk/forums/showthread.php?t=183541

 

[lechu.555]

I don't think I completely understand what you are currently doing. Is DateFied2 also empty when the Balance field is empty?

Yes, it's empty; I created it to extract the latest date where Balance was not empty. So, that in another query I can DLookUp to extract and plug the last balance into the empty Balance record.

Thanks for following up on this.
lechu.555