help copying over null records

[Cansley]

Hi
I have to clean up an imported table that only has a cost center (1st field) on the first record (monday) of a weeks worth of data. The other four days have null values until the next monday come around. I need a way to fill in the cc number on the other four days, then reset it to the new cc number (on the next occurnace) until all records have been processed. Much appreciate any help.
Regards,
Clarence

 

[Fizzio]

It sounds like the cc changes every week.
If the cc never changes, then you can use an update query to update the cc to whatever value you like.

If it does, you will need to use VB code to loop through all the values and update the cc as necessary. In basics you will need to

Check if the cc is filled in
If so, store the cc value and skip to next record
If not filled in, copy that stored value to the cc and move to new record
etc..

You will need to use a recordset and a loop to process all the records. If you need help with this, post back.

 

[Cansley]

First of all, thanks for replying.

I have been trying to do as you suggest and have hit a snag.
this what I have so far

Sub copyloctions()

'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String
Dim strSQL1 As String
Dim plug As Integer
Dim counter


'Establish the connection, cursor type,
'and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from Master")

strSQL = "location > 1"
strSQL1 = "location < 1"


'Loop through the recordset, locating all rows meeting
'the designated criteria,

rst.Find strSQL

Do Until rst.EOF
plug = rst("location")
rst.Find strSQL1

Do Until rst.EOF
If rst("location") > 1 Then GoTo exit1
rst("location") = plug
rst.Update
rst.Find strSQL1, 1, adSearchForward
Loop
exit1:

rst.Find strSQL, 1, adSearchForward

Loop


rst.Close
Set rst = Nothing

End Sub


location is the CC. I know I have a probem with the second loop. When I run this it takes the first occurance of a cc and then populates all empty CCs with this number


if raw data is:

10
0
0
0
20
0
0
0

I need:

10
10
10
10
20
20
20
20


and I get:

10
10
10
10
20
10
10
10

any help is very much appreicated!

thanks again

 

[Fizzio]

Instead of this
rst.Find strSQL

Do Until rst.EOF ...........}This is the reason you are getting your
plug = rst("location") ...}results as it is not stopping when it finds
rst.Find strSQL1 ...........}a non-zero result.

Do Until rst.EOF
If rst("location") > 1 Then GoTo exit1
rst("location") = plug
rst.Update
rst.Find strSQL1, 1, adSearchForward
Loop
exit1:

rst.Find strSQL, 1, adSearchForward

Loop


Try This

'Loop through the recordset

rst.Find strSQL 'Find First Non-Zero

Do Until rst.EOF

If rst("location") > 1 Then 'ie CC >0
plug = rst("location") 'copy CC to plug
else 'If cc = 0
rst("location") = plug 'copy plug value to location
rst.Update
rst.movenext
Loop

hth

 

[Cansley]

Thanks again

very helpful
thanks so much