473,722 Members | 2,325 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA Function to to fill nulls/copy down values

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

May 22 '07 #1
6 5451
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


May 23 '07 #2
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.

May 23 '07 #3

"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
May 23 '07 #4
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

May 23 '07 #5
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?

May 24 '07 #6
"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.

May 24 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1640
by: RickL | last post by:
I am new to .NET (and directory services) and I need to fill a drop down list box with the users of my company. I am trying to use the code shown below, but I get this error message when the line of code with the "For" statement runs: "System.Runtime.InteropServices.COMException: The specified domain either does not exist or could not be contacted Can anyone give me any ideas as how to resolve this? Any and all help would be greatly...
3
17156
by: M | last post by:
I am trying to copy the values of one hashtable to another. I ran into a read only error when using the IDictionaryEnumerator. while (myHashEnumerator.MoveNext()){ while (fsHashEnumerator.MoveNext()){ if (myHashEnumerator.Key == fsHashEnumerator.Key) { myHashEnumerator.Value = fsHashEnumerator.Value; }
4
4230
by: Tamer via DotNetMonster.com | last post by:
I'm developing a graphic engine. I got all equations made, I need only to understand how I can create a function that, once accepted three values (x; y; z), it returns the two values (X; Y) coming from two distinct mathematical processes. I don't want to use another global variable, I'm trying to be fast, in order to use such a function even in this way: Public Structure Point x as double y as double z as double
1
2124
by: MasterChief | last post by:
I have an asp page right now and I want to fill a drop-down list with information like a persons name from an xml file. Once the user picks a person from the drop down list I want it to go back to the xml file and load the information about the person into textboxes. Does anybody have an example for something like this in asp?
0
3299
by: --Grrrrrr-- | last post by:
Bit of a novice to this and, even worse, self-taught so could be missing something really obvious. Anyway, would very much appreciate help with: Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of...
3
2083
by: Senna | last post by:
Hi Have these to work with. int counter = 0; //The total item to fill the collection with int increment = 2; //Any number int current = 244; //Quantity int max = 1290; //Max Quantity int min = 0; //Min Quantity, is 0 or the increment value List<intlist = new List<int>(); //A simple collection
3
7359
by: penny111 | last post by:
Hi there, For my application, i need to have 3 drop down lists 1. drop down list of folder names 2. drop down list of documents in the folder selected 3. drop down list of instances of the document selected (my application uses the BusinessObjects Java Web Services SDK) The 2nd list is dependent on the 1st, while the 3rd list is dependent on the 2nd. In other words, this is what i want my application to do -select a folder from the...
7
3900
by: himanshupancholi | last post by:
Hi, I need to Copy all values of a column from one table to another. Below are the details: Source: STL_GRP table, VEND column Destination PARTNER table, VEND column. I am using the below query which is incorrect: UPDATE PARTNER SET VEND=
0
8739
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9382
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9236
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9087
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8043
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5995
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2601
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.