Dlookup to update null cells

[knoll126]

Hi,

This morning I have been looking all around for an answer and I feel like I am almost there, but I am getting a Syntax error. My table named "MonthlySalesTax" is setup as below.

ID	Ship_To_City	Ship_To_State
1	Seattle	WA
2
3
4	San Francisco	CA
5
6

<tbody>
</tbody>

I want the query to auto fill the Ship_To_Ctiy and Ship_To_State if null to the above row, so Seattle and WA would fill down until it his San Francisco and CA. then San Francisco and CA would auto fill down until the next one. After researching it seemed like an update query with dlookup would work best. Here is what I have, but it comes back with and error. Any idea where the error lies or if I am approaching this incorrectly.

UPDATE MonthlySalesTax SET MonthlySalesTax.[Ship_To_State] =
DLookUp("[Ship_To_State]","MonthlySalesTax","[ID] = [ID]-1"),
MonthlySalesTax.[Ship_To_City] =
DLookUp("[Ship_To_City]","MonthlySalesTax","[ID] = [ID]-1"),
WHERE (((MonthlySalesTax.[Ship_To_State]) Is Null)) OR
(((MonthlySalesTax.[Ship_To_City]) Is Null));

Thanks,
Noel

 

[knoll126]

I have got it to work. Thank you. If I have any more issues I will let you know.

 

[xenou]

For kicks, you can try this (on a backup first) to see how it performs:

update t1
set t1.Ship_To_City = t2.Ship_To_City,
    t1.Ship_To_State = t2.Ship_To_State
from 
    MyTable t1
    inner join MyTable t2
    on t1.ID = (t2.ID - 1)
where    
    nz(t1.Ship_To_City) = ""
    and nz(t1.Ship_To_State) = ""

Would be interesting to see if it works (i.e., using a self-join, so not need to copy table etc. etc).

 

[Joe4]

Out of curiosity, what was causing the error?

If you try both methods, I am curious to know how long each one takes. Someone once told me that the Recordset approach would be faster, but I never had enough records to confirm that. With over 3 million records, that would be a pretty good test!

 

[knoll126]

Out of curiosity, what was causing the error?

If you try both methods, I am curious to know how long each one takes. Someone once told me that the Recordset approach would be faster, but I never had enough records to confirm that. With over 3 million records, that would be a pretty good test!

I haven't tried xenou's method, but if I get time I will and update. With Recordset it takes only a few minutes to update all those null cells. There are about ~3 nulls cells repeating every ~4 rows. Seems relatively fast to me.

 

[xenou]

Actually don't try me - it's a fail. The query will only work if there is one empty row to fill (So, actually works only if you run it iteratively - but that means it will be slow, you'd have to run it several times at least - though still in the realm of feasibility if you have to use sql and there only gaps of 3-4 rows to fill).