By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,253 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,253 IT Pros & Developers. It's quick & easy.

Filling In Blank Field Information

P: n/a
Dear fellow Access 2000 users:

I have a table where there are 5 columns and thousands of rows of
data. Every so often, there is missing data in the second and third
column. I wanted to write some code that would, when it came to a
blank "cell" in column two or three, to simply copy whatever was in
the cell above it.

For instance, here is an example of a few rows before the code is run:
A B C D E
WW WW WW WW WW
XXX XXX XXX XXX XXX
YYY YYY YYY
ZZZ ZZZ ZZZ ZZZ ZZZ

Here is what I am looking for after:

WW WW WW WW WW
XXX XXX XXX XXX XXX
YYY XXX XXX YYY YYY
ZZZ ZZZ ZZZ ZZZ ZZZ

(Basically copy the XXX data down to the blank YYY row)

I searched Google, but could not find anything that did such a task.

If anyone could possible come up with some code, I would GREATLY
appreciate it!

Thanks a million in advance!

Kevin
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Kevin,

I think you're right in that you'll need some code to do this: the basic framework would be something like the following (please note, I haven't tested it and you'll probably need to distinguish between nulls and empty strings, but it should give you enough to work on). Let me know how you go.

Set RS = CurrentDb.OpenRecordset("Table1", dbOpenTable, dbForwardOnly, dbOptimistic)

Do Until RS.EOF

If RS.Fields("Field1").Value Is Null Then
RS.Fields("Field1").Value = SavedField1Value
Else
SavedField1Value = RS.Fields("Field1").Value
End If

If RS.Fields("Field2").Value Is Null Then
RS.Fields("Field2").Value = SavedField2Value
Else
SavedField2Value = RS.Fields("Field2").Value
End If

etc

RS.MoveNext

Loop

RS.Close
Set RS = Nothing

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

P: n/a
Oop - you'll definitely need an RS.Update in there too, or your changes won't be saved...

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.