Fill Down a Column

[Guest]

I have a column in my table that contains blank values. Those blank values
are related to the record directly above the blank value. I want to be able
to use an update query to fill in the blanks. I did this once before, but I
can't remember how I did it. I remember it involved creating a function in
VB and then calling that function as part of my update query. The function
basically stated " set the first record as the one to copy to the next record
if the value of the second record is null". It looked for null values and
filled in the null field with the field data directly above it. It worked
beautifully. Has anyone ever had occasion to use that type of function in
conjunction with an update query?

 

[John Vinson]

I have a column in my table that contains blank values. Those blank values
are related to the record directly above the blank value.

Ummm... No, they aren't, not in a properly normalized table.

A table HAS NO ORDER. There *is* no such concept as "the record
directly above".

You can't count on the records being presented in any particular
order, unless you use a Query sorting the records.

John W. Vinson[MVP]

 

[Guest]

Yes you can. When your first column is an autonumber as the ID and Primary
Key with no duplicates, the records appear in ID sort order every time. My
table didn't have a changce to be a "properly normalized table". The blank
values are the result of an import from a txt file. I've actually done this
before. When I figure out what I did before, I'll post it. That way you can
put it in your own bag of tricks. Once you see what I'm talking about, your
going to love it. And I suspect you'll use it all the time.

 

[Guest]

Eurika! I finally remembered what I did. I'm going to post it here, so you
can use it, and even pass it on if you find anyone ever asks this quesion
again... (because your response wasn't really helpful, and I thought the
point of this comunity was to be helpful... and your tone was not helpful
either)...

My table looks something like this:

Order Date PO Description Qty ExtSell ExtCost
ExtGP
S2281331 6/1/06 XYZ GHDIUDUUFU 2 3.00 1. 00 2.00
CLSIDNGNDI 3 4.00 2.00
2.00
PDOINDNF 1 2.00 1.00
1.00
S2283746 6/1/06 ABC POINDNFI 2 2.00 1.00 1.00

And so on.....

I want to fill in the blank fields with the same field in the record
directly above it in data sheet view (but only when it's blank)

I created the following VB code:

Option Compare Database
Option Explicit
Public HoldOrder As String
Public HoldDate As Date
Public HoldPO As String
---------------------------------------------------------
Public Function FillOrder(ID) As String
If IsNull(ID) Then Else HoldOrder = ID
FillOrder = HoldOrder
End Function
---------------------------------------------------------
Public Function FillDate(ID) As Date
If IsNull(ID) Then Else HoldDate = ID
FillDate = HoldDate
End Function
---------------------------------------------------------
Public Function FillPO(ID) As String
If IsNull(ID) Then Else HoldPO = ID
FillPO = HoldPO
End Function
--------------------------------------------------------

Then, in my update query, I built it this way:

UPDATE [Sdge-open] SET [Sdge-open].[Order] = FillOrder([Order]),
[Sdge-open].[Date] = FillDate([Date]), [Sdge-open].PO = FillPO([PO]);

Turns out you don't actaully need there to be an "ID" column with autonumber
etc. This VB code in conjunction with the update query works beautifully.
It works every time. Try it. I think you'll like it.

 

[dbahooker]

you do need a primary key.

dont ever build a single table without a pk and don't talk back to
people that help you


-Aaron

Eurika! I finally remembered what I did. I'm going to post it here, so you
can use it, and even pass it on if you find anyone ever asks this quesion
again... (because your response wasn't really helpful, and I thought the
point of this comunity was to be helpful... and your tone was not helpful
either)...

My table looks something like this:

Order Date PO Description Qty ExtSell ExtCost
ExtGP
S2281331 6/1/06 XYZ GHDIUDUUFU 2 3.00 1. 00 2.00
CLSIDNGNDI 3 4.00 2.00
2.00
PDOINDNF 1 2.00 1.00
1.00
S2283746 6/1/06 ABC POINDNFI 2 2.00 1.00 1.00

And so on.....

I want to fill in the blank fields with the same field in the record
directly above it in data sheet view (but only when it's blank)

I created the following VB code:

Option Compare Database
Option Explicit
Public HoldOrder As String
Public HoldDate As Date
Public HoldPO As String
---------------------------------------------------------
Public Function FillOrder(ID) As String
If IsNull(ID) Then Else HoldOrder = ID
FillOrder = HoldOrder
End Function
---------------------------------------------------------
Public Function FillDate(ID) As Date
If IsNull(ID) Then Else HoldDate = ID
FillDate = HoldDate
End Function
---------------------------------------------------------
Public Function FillPO(ID) As String
If IsNull(ID) Then Else HoldPO = ID
FillPO = HoldPO
End Function
--------------------------------------------------------

Then, in my update query, I built it this way:

UPDATE [Sdge-open] SET [Sdge-open].[Order] = FillOrder([Order]),
[Sdge-open].[Date] = FillDate([Date]), [Sdge-open].PO = FillPO([PO]);

Turns out you don't actaully need there to be an "ID" column with autonumber
etc. This VB code in conjunction with the update query works beautifully.
It works every time. Try it. I think you'll like it.

-----------------------------------------------------------------------------------------------

Ummm... No, they aren't, not in a properly normalized table.

A table HAS NO ORDER. There *is* no such concept as "the record
directly above".

You can't count on the records being presented in any particular
order, unless you use a Query sorting the records.

John W. Vinson[MVP]