Equivalent of Excel's fill down command

[Dawnit]

Any way to fill several records in one field with info in a record above the empty record fields?

 

[tlbroadbent]

Write a query.

Update table1 set field1=(Select field1 From table1 where <enter selection criteria for source row>) Where Field1 Is Null Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

Terry,

UPDATE tblTestFillDown SET tblTestFillDown.fldTest = (Select Max(fldTest) From tblTestFillDown) + 1
WHERE (((tblTestFillDown.fldTest) Is Null));

doesn't work. Error is &quot;Must use updateable Query&quot;

So I tried more like what you posted, as in:

UPDATE tblTestFillDown SET tblTestFillDown.fldTest = (Select fldTest From tblTestFillDown Where fldTest = Max(fldTest)) + 1
WHERE (((tblTestFillDown.fldTest) Is Null));

But this ver say &quot;Can't have aggregate in where clause&quot;

Any suggestions?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

The MAX is unnecessary because you should be selecting one record. Try using the DLookUp function.

UPDATE tblTestFillDown
SET tblTestFillDown.fldTest = (DLookUp(&quot;[fldTest]&quot;,&quot;tblTestFillDown&quot;,&quot;RecID=1&quot
WHERE (((tblTestFillDown.fldTest) Is Null));

&quot;RecID=1&quot; is the only thing you should need to change. You need to identify the source record.

This works to solve the requirement described in your first post - &quot;fill several records in one field with info in a record above.&quot;

However, it appears the requirement is different. Do you really want to fill the null or empty columns with an increasing value, starting with the current maximum value + 1? Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

That is apporximatly the simple version of the excel function. From Excel Help:

Fill in a series of numbers, dates, or other items
Select the first cell in the range you want to fill, and then enter the starting value for the series.
To increment the series by a specified amount, select the next cell in the range and enter the next item in the series. The difference between the two starting items determines the amount by which the series is incremented.

Select the cell or cells that contain the starting values.


Drag the fill handle over the range you want to fill.
To fill in increasing order, drag down or to the right.

To fill in decreasing order, drag up or to the left.

Notes

To specify the type of series, use the right mouse button to drag the fill handle over the range, and then click the appropriate command on the shortcut menu. For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-2002, MAR-2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on.


To manually control how the series is created, or use the keyboard to fill in a series, use the Series command.


End Of Quote.

So, The Fill value needs to change for each (Cell) - or in database land Record. Doing it w/ a module (VBA) is somewhat trivial, however seeing your post using a query reminded me of the several issues in that approach - not the least of which is the time consumed.

Hoping that you had a better way.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over