On May 22, 6:33 pm, Clif...@gmail.com wrote:
I need to fill in the nulls in the batch field the value from the
record immediately
preceding the null one ie replace the nulls with the preceding value
until I hit a record with a value in it--then hold the next value
through the next set of nulls, and so on.
See example below:
I wanna copy down batch "IMR138" in record ID 1, all the way to ID 10.
Then copy down batch "7138" all the way to ID 20 and so on....
ID BATCH
1 IMR138
2
3
4
5
6
7
8
9
10
11 7138
12
13
14
15
16
17
18
19
20
21 7141
22
23
24
25
26
27
28
29
30
31 04A49DS2007R
32
I see you've requested a VBA function, but here's an alternate way to
do it using SQL:
tblBatches = the fields and data from your example
qryBatch1:
SELECT ID, (SELECT A.BATCH FROM tblBatches AS A WHERE A.ID = (SELECT
MAX(B.ID) FROM tblBatches AS B WHERE B.ID <= tblBatches.ID AND B.BATCH
IS NOT NULL)) AS NewValue
FROM tblBatches;
!qryBatch1:
ID NewValue
1 IMR138
2 IMR138
....
11 7138
12 7138
....
21 7141
22 7141
....
31 04A49DS2007R
32 04A49DS2007R
You can add that subquery to any queries you need for reports or, if
needed, update tblBatches with the values.
Note that initial Null values do what you'd expect -- they stay Null.
If qryBatch1 is changed into a Make Table query, say, tblBatchChange
then a second query can change the values in tblBatches:
qryBatch2:
UPDATE tblBatches INNER JOIN tblBatchChange ON tblBatches.ID =
tblBatchChange.ID SET tblBatches.BATCH = tblBatchChange.NewValue;
Perhaps this can be done in a single update query. Maybe I'll give
that a shot. Note that I'll exhaust the query capabilities of A97
before resorting to the capabilities added to later versions of
Access.
James A. Fortune
CD********@FortuneJames.com