Retrieve Prior Non-Null Value from Table 1

[DougTucker]

I'm manipulating a data extract that defines each new customer record in a header row, but doesn't attach the customer name to each record. I need to create a key lookup table by populating a "customer name" value for every row in the customer record until another customer name (header) appears.

This sounds similar to that posted by MCuthill (posting

http://www.tek-tips.com/viewthread.cfm?qid=1555695&page=1)

I can accomplish this really easily in MS Excel by comparing the current rows to the prior row. If the customer name is null, pull from the prior row. Eventually the prior row will be the header row (see attached .xls).

I want to have a VBA procedure that I can call to manipulate a file that starts with this:
************
RecID Company
1 Acme
2
3
4
5 Jones
6
7
8
9 Smith
10
11

************
The resulting data should look like this:
RecID Company
1 Acme
2 Acme
3 Acme
4 Acme
5 Jones
6 Jones
7 Jones
8 Jones
9 Smith
10 Smith
11 Smith

Your help is greatly appreciated!
~ Doug T.

 

[PWise]

dim mydb as database
dim rst as recordset
dim Company as String
set mydb=currentdb
set rst = mydb.openrecordset("Select * from tablename")
Company = rst!Company
rst.edit
do while not rst.eof
if nz(rst!Company,"")=""then
rst!Company=Company
rst.update
else
Company=rst!Company
end if

rst.movenext
loop

 

[PWise]

sb

set rst = mydb.openrecordset("Select * from tablename order by recid")

 

[dhookom]

You should be able to use a query with syntax like:

UPDATE DougTucker SET DougTucker.Company = DLookUp("Company","DougTucker","RecID =" & DMax("RecID","DougTucker","RecID<=" & [RecID] & " AND Company Is Not Null"))
WHERE DougTucker.Company Is Null;

Duane
Hook'D on Access
MS Access MVP

 

[DougTucker]

Duane: Your SQL is pretty much doing the job. I'm currently tweaking it to ignore the first few rows, before the first company name appears. The actual text looks like the following, and produces "#Error" for the value on the first two rows:
1
2
3 Acme
4
5
6
7 Jones
8
9 Smith
10
11

I'll let you know what I find. If you have an easy solution please reply with a code tweak.

Thank you!
~ Doug

 

[DougTucker]

The following is sufficient for retrieving a NULL if the first (header) Customer Name is not yet populated (it basically looks at the value returned from "DMax", and if null, just return a null and don't process the remainder of the code):

Company Name: IIf(DMax("[RecID]","[MyDatabase]","[RecID]<=" & [RecID] & " AND [Company Name_Temp]") Is Not Null,DLookUp("[Company Name_Temp]","[MyDatabase]","[RecID] =" & DMax("[RecID]","[MyDatabase]","[RecID]<=" & [RecID] & " AND [Company Name_Temp] Is Not Null")),Null)

That seems to be working for now. Thank you all for your help!

~ Doug T.

 

[DougTucker]

The solution by Dhookom works fine for small data sets, but with my table of several 1000 records it's getting bogged down (takes over an hour to generate the data and append to a new table). I'll try the code that Pwise posted. I'll let you know how it works.
~ Doug T.

 

[DougTucker]

Given the size of my data, the module from PWISE works really well. The query alone (with DLOOKUPs) took over an hour. The module took about 15 seconds.

The only change I had to make to the code (other than customizing the variable names) is to add "rst.Edit" before "rst!Company=Company". For some reason it threw an error having this outside of the WHILE loop.

THANK YOU for all of your great help!
~ Doug T