I have imported records into a table and some of the records have empty fields. All of the blank fields (number and name) belong to the previous record. For example:

Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
                       Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
                       Records 1400
                       Business 1700
66666 David Medical 0800

I want to find a way to populate this table and eliminate all blank fields. I have tried the Dlookup to find the null values, but I cannot find a way to copy it into the appropriate record.

The tble should look like this:

Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
22222 Jane Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
55555 Julie Records 1400
55555 Julie Business 1700
66666 David Medical 0800


Any help and guidance willbe greatly appraciated.

Thank you

My approach to problems like this is the export it to Excel, manipulate it, and then import it back into Access.

In Excel you would have:
  A B C D
Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
                  Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
                  Records 1400
                  Business 1700
66666 David Medical 0800

I would do Column E as New Number and F as New Name.

In Column E do something like =IF(A2="", E1, A2)
& in F something like =IF(B2="", F1, B2)

Then just drag those formulas down, and it should give you a solid list of Numbers and Names. Select Column E & F, Copy them, then Right Click on E1 and choose Paste Special, and choose the Values Option. Then you can copy E & F over A & B, Erase E&F and then import the sheet into Access.

Hope that helps,

Mike

Mike
EchoVue.com

Thank you for your suggestion, the thing about transfer it to excel is that the data importing is a task to be performed everyday by a person who is not very computer savy. I want to make the process with the least amount of user interaction to prevent and minimized errors. The initial data is coming already to Access from a .txt tab delimited file. Can your formula be used, let's say in a VBA module?

Aah, the user - the weakest link of any system!!

I have done something like this in VBA before. What you want to do, is set up some variables for the current line, and some for the name & Number of the previous line. Read each line in, then if the Name and Number fields are blank, set the current name and number = to the previous one, otherwise, copy the new name and number onto the previous variables. The set up an INSERT SQL satement, and put it into the table.

Mike


Mike
EchoVue.com

I figured out a way with an update query, a dlookup statement in the "update to" field and an "is null" statement in the criteria field.....

Thanks for the help

Would you please show me how you did this? I have to do the same for a table. Thanks.

Here's how I've solved it:

in the Update query in design view:
select your field and your table.
In the "Update to:" field:
DLookUp("[YOURFILED]";"YOURtableNAME";"[ID] = " & [ID]-1)
"Criteria" field:
Is Null

This is working for me...hopefully you'll get it to work also.