VBA Function to to fill nulls/copy down values

[Cliff72]

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

[Steve]

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim NullReplace As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordse t("QryIDBatch ")
Do Until Rst.EOF
If Rst!Batch = Null Then
Rst!Batch = NullReplace
Else
NullReplace = Rst!Batch
End If
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
Set DB = Nothing

Note 1: The assumption from your post is that Batch in the first record is
not null. The code above will leave the first X records null if they are
initially null.

Note 2: The above code contains no error handling.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com


<Cl*****@gmail. comwrote in message
news:11******** **************@ q66g2000hsg.goo glegroups.com.. .

>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

[Cliff72]

On May 22, 6:17 pm, "Steve" <s...@private.e mailaddresswrot e:

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim NullReplace As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordse t("QryIDBatch ")
Do Until Rst.EOF
If Rst!Batch = Null Then
Rst!Batch = NullReplace
Else
NullReplace = Rst!Batch
End If
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
Set DB = Nothing

Note 1: The assumption from your post is that Batch in the first record is
not null. The code above will leave the first X records null if they are
initially null.

Note 2: The above code contains no error handling.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resou...@pcdata sheet.com

<Clif...@gmail. comwrote in message

news:11******** **************@ q66g2000hsg.goo glegroups.com.. .

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- Hide quoted text -

- Show quoted text -

Thanks for you response Steve. This didnt work.

[Arno R]

"Steve" <so***@private. emailaddresssch reef in bericht news:iC******** **********@news read2.news.pas. earthlink.net.. .

Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim NullReplace As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordse t("QryIDBatch ")
Do Until Rst.EOF
If Rst!Batch = Null Then
Rst!Batch = NullReplace
Else
NullReplace = Rst!Batch
End If
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
Set DB = Nothing

Note 1: The assumption from your post is that Batch in the first record is
not null. The code above will leave the first X records null if they are
initially null.

Note 2: The above code contains no error handling.

Hi Steve, back in town as we have noticed ??
It's a pity but your code is *very* bad for a (so-called) resource...

Note1: Strings can not be null
You need a line like NullReplace = Nz(Rst!Batch,"" )
Note2: The line Rst!Batch = NullReplace is *never* executed
You need a line like If IsNull(rst!Batc h) = true then ....
Note3: *If* the line Rst!Batch = NullReplace would be executed it would error
Of course you would need an rst.Edit and rst.Update also...

Note 4: Please do NOT advertise here !! (we have seen you...)
Same old story ?? Please, please don't !! Nobody is going to like that !!
To the OP: Code that will work:
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim NullReplace As String
Set DB = CurrentDb()
Set Rst = DB.OpenRecordse t("TabIDBatch ")
Do Until Rst.EOF
If IsNull(Rst!Batc h) = True Then
Rst.Edit
Rst!Batch = NullReplace
Rst.Update
Else
NullReplace = Nz(Rst!Batch, "")
End If
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
Set DB = Nothing

Arno R

[CDMAPoster]

On May 22, 6:33 pm, Clif...@gmail.c om 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.BATC H = 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********@Fort uneJames.com

[Cliff72]

On May 23, 1:49 pm, CDMAPos...@Fort uneJames.com wrote:

On May 22, 6:33 pm, Clif...@gmail.c om 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.BATC H = 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
CDMAPos...@Fort uneJames.com- Hide quoted text -

- Show quoted text -

Thanks for your response I will try that too. Actually, this data is
imported from a fixed width text file now, i don't know if it would be
easier to write code that would read the text file line by line and
then updating the nulls in the process before importing it into a
table?

[Keith Wilby]

"Arno R" <ar************ ****@planet.nlw rote in message
news:46******** *************** @text.nova.plan et.nl...

It's a pity but your code is *very* bad for a (so-called) resource...

But his sig line is meticulously checked for accuracy before posting.

Keith.