Fill Down Empty Records

[dcanfield]

I have a situation where I want to find the first record that has a blank (or null) value (in a certain field), and then copy the value of the record right above it.

Here is a sample type of data file:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B
1234 C
1234 D
5678 A 05/10/05
5678 B
5678 C
5678 D

In this example, I have a file of sales orders. On the first record for a customer order, it has the date of the sale for the first item. For all remaining items sold on that order, the date of sale field is blank (null). I want to be able to find all of the blank dates, and fill in the date from the first record, so that I can report on sales by date for selected items. In this case, I would like the macro to be able to find the first blank (order 1234, Item B), and copy the date from Item A (05/09/05) into the Date Sold field for Item B, then C, then D. Then it would find Order 5678, Item B, and copy the Date Sold (05/10/05) from Item A into the Date Sold field of Item B, then C, etc.

The completed file would look like:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B 05/09/05
1234 C 05/09/05
1234 D 05/09/05
5678 A 05/10/05
5678 B 05/10/05
5678 C 05/10/05
5678 D 05/10/05


I have a Macro in MS Excel that does this, but some of my files are too big to fit into a spreadsheet, so I want to be able to perform the same functionality in Access.

Is there a way to create a macro in Access that would perform this function?

Thanks,

David

 

[PHV]

You may try the SQL way:
UPDATE yourTable A INNER JOIN yourTable B ON A.[Order number] = B.[Order number]
SET A.[Date Sold] = B.[Date Sold]
WHERE A.[Date Sold] Is Null AND B.[Date Sold] Is Not Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dcanfield]

Looks like I need to clarify my original post...

I only have ONE table...that has all of the above fields.

A better way to describe what I needed might be:

I currently have a MS Access table that gives a field value when it occurs for the first time but not subsequent occurrences - ie. the blank cells underneath, in the subsequent rows/records are assumed to have the same value as above.

In essence I'm looking for a macro/global way of finding all such blank (Null) cells, and then using the function {Ctrl} + {' } where the cell I'm in is blank.

Thanks for any assistance you can provide.

David

 

[PHV]

I only have ONE table
My suggested Update query use only ONE table

 

[lespaul]

UPDATE yourTable A INNER JOIN yourTable B ON A.[Order number] = B.[Order number]
SET A.[Date Sold] = B.[Date Sold]
WHERE A.[Date Sold] Is Null AND B.[Date Sold] Is Not Null;

replace both instances of yourTable with the correct tablename and run the query!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual