Data frm Previous Record to fill blanks

[Eric Sandin]

Hi.

I have been searching the web for a solution and found this wonderful site and although there are many examples in the forum about "find the data from a previous record", none have been able to help with my specific issue.

I have a table of dates (some cells are null) and need to auto fill the null fields with the data from above the null value.

So basically, I need to loop through the list and where there is a empty cell, copy/paste the data that is above the empty cell into the empty cell and continue on until the end of the list. If there are two or more empty/null values the last active date needs to be copied into the cell.

I have been able to work out a macro in Excel to do this with a "short-cut" key but it 1) requires that I hit the short cut key each time it hits an empty cell to loop through the list and 2) limits the amount of rows I can have in my table.

So, I'm now trying to do the same thing in Access that will loop through all the rows until the end.

As an example, if the data starts like this:

ID-Date

01-06/20/2010
02-
03-07/01/2009
04-
05-
06-01/01/1999
07-
08-
09-06/10/2009
10-
11-

I need it to end up like this:

01-06/20/2010
02-06/20/2010
03-07/01/2009
04-07/01/2009
05-07/01/2009
06-01/01/1999
07-01/01/1999
08-01/01/1999
09-06/10/2009
10-06/10/2009
11-06/10/2009


Any help would be greatly appreciated!

Thank you!!

 

[pbaldy]

You could open a recordset on the table, sorted by ID. Put the date from the first record into a variable. In a loop of the recordset, use an If/Then/Else block. If the date in the current record is blank, update it with the variable's value. Otherwise, update the variable to the record's value.

 

[Eric Sandin]

Thank you, Paul. This sounds like exactly what I need to do. I'm not the best at creating records sets and If/Then/Else blocks in Access but I'm going to give it a go using examples of other code from here on the forum pages. I will let you know how it goes and post the code here for others if I'm able to get it done correctly.

Thank you again!!
Best,
Eric

 

[pbaldy]

Happy to help Eric; post back if you get stuck. Best way to learn how to swim is to get thrown in the pool!

 

[Eric Sandin]

Paul,

How do I "Put the date from the first record into a variable?"

Here is what I've gotten to wor so far (I know it not much):



Sub Sort()


Dim Rs As Recordset
Dim RsSql As String

'Create a snapshot of data from your table with the desired fields and data
RsSql = "Select ID, SORTDATE1 From Dates2 Where ID IS NOT NULL"
'Where Condition = '" & Criteria & "'"
'Open the recordset
Set Rs = CurrentDb.OpenRecordset(RsSql)





'Destroy the instance of the recordset from memory
Set Rs = Nothing
End Sub

 

[pbaldy]

It would look like:

VariableName = Rs!SORTDATE1

Make sure you add an ORDER BY clause to your SQL. You want to make sure the records are in the expected order. You don't want to assume that because you see them in the table in order that the recordset will pull them in the same order. Other tools you'll need are a loop (Do While Not Rs.EOF) and the Edit method of the recordset.

 

[Eric Sandin]

Got it, thanks!! Will do on the ORDER BY clause.
Thank you again for all of your help!!

 

[pbaldy]

No problemo. We want you splashing around in the pool but keeping your nose above water. Post back if you start sinking.

 

[The_Doc_Man]

Here's a word of warning.

If those records could have arrived in an oddball order, you won't get the results you want. The order of the records in a table is not predictable unless there is another variable to enforce ordering such that you can force the recordset to be in the right order.

Stated another way, if you DON'T have an ordering variable, you will get results but there is no guarantee that they will be right.