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]

Definitely interested.

 

[Joe4]

I'll see if I can dig it up and come up with some code to give you a little later on.
In the meantime, here is another non-VBA method you may want to play around with: Microsoft Access tips: Subquery basics

Let me know if you get it to work.

 

[xenou]

if you copy your table, you can try this join (you also get a copy of the table as your backup ):

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

It should be possible without a copy of the table (self-join) but I hope that Access will go faster this way. Make sure ID is either a primary key or an indexed field.

 

[knoll126]

if you copy your table, you can try this join (you also get a copy of the table as your backup ):

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

It should be possible without a copy of the table (self-join) but I hope that Access will go faster this way. Make sure ID is either a primary key or an indexed field.

Sorry if I am not completely understanding this, but do you mean before running this code, go into access right click copy and paste the table?

 

[Joe4]

OK, here is the DAO method I came up with.

First, create a new query named "Sorted_Query". Simply add the fields from your MonthlySalesTax field, and sort by the ID field.

Now, go into the VB Editor (Alt-F11), right-click in the Project Explorer and click Insert then Module.
Then post the following VBA code in the VBA editor window:

Public Function UpdateNulls()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim ShpCity As String
    Dim ShpState As String
    Dim PrevShpCity As String
    Dim PrevShpState As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Sorted_Query", dbOpenDynaset)
    ShpNo = ""
        
    rst.MoveFirst
    Do While Not rst.EOF
'   Set previous values for next run
        PrevShpCity = ShpCity
        PrevShpState = ShpState
'       Compare Ship_To_City to previous value
        ShpCity = Nz(rst.Fields("Ship_To_City"), "")
        If ShpCity = "" Then
            ShpCity = PrevShpCity
        End If
'       Compare Ship_To_State to previous value
        ShpState = Nz(rst.Fields("Ship_To_State"), "")
        If ShpState = "" Then
            ShpState = PrevShpState
        End If
'       Write values to fields
        With rst
            .Edit
            .Fields("Ship_To_City") = ShpCity
            .Fields("Ship_To_State") = ShpState
            .Update
            .MoveNext
        End With
    Loop
    
    rst.Close
    db.Close
               
    MsgBox "Update complete!"
    
End Function

Then, go back out to the Access menu, go to the Create menu and select Macro.
Choose the RunCode action (may need to click the "Show All Actions" button), and browse to the UpdateNulls function we just created.
Then just click on the Run button, and it should run!

Note if you get a VBA error about a missing reference or Library, go back in to the VBA editor, go Tools, then References and select the library that is named something like Microsoft DAO 3.6 Object Library (version number may differ slightly).

 

[xenou]

Sorry if I am not completely understanding this, but do you mean before running this code, go into access right click copy and paste the table?

Yes, that is a good way to copy a table.

 

[knoll126]

OK, here is the DAO method I came up with.

First, create a new query named "Sorted_Query". Simply add the fields from your MonthlySalesTax field, and sort by the ID field.

Now, go into the VB Editor (Alt-F11), right-click in the Project Explorer and click Insert then Module.
Then post the following VBA code in the VBA editor window:

Public Function UpdateNulls()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim ShpCity As String
    Dim ShpState As String
    Dim PrevShpCity As String
    Dim PrevShpState As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Sorted_Query", dbOpenDynaset)
    ShpNo = ""
        
    rst.MoveFirst
    Do While Not rst.EOF
'   Set previous values for next run
        PrevShpCity = ShpCity
        PrevShpState = ShpState
'       Compare Ship_To_City to previous value
        ShpCity = Nz(rst.Fields("Ship_To_City"), "")
        If ShpCity = "" Then
            ShpCity = PrevShpCity
        End If
'       Compare Ship_To_State to previous value
        ShpState = Nz(rst.Fields("Ship_To_State"), "")
        If ShpState = "" Then
            ShpState = PrevShpState
        End If
'       Write values to fields
        With rst
            .Edit
            .Fields("Ship_To_City") = ShpCity
            .Fields("Ship_To_State") = ShpState
            .Update
            .MoveNext
        End With
    Loop
    
    rst.Close
    db.Close
               
    MsgBox "Update complete!"
    
End Function

Then, go back out to the Access menu, go to the Create menu and select Macro.
Choose the RunCode action (may need to click the "Show All Actions" button), and browse to the UpdateNulls function we just created.
Then just click on the Run button, and it should run!

Note if you get a VBA error about a missing reference or Library, go back in to the VBA editor, go Tools, then References and select the library that is named something like Microsoft DAO 3.6 Object Library (version number may differ slightly).

I am able to copy the code into the VBA window and add in RunCode as a Macro, but I do not see a place to browse for UpdateNulls. I am able to type in the field and type "UpdateNulls" for the function name, but it doesn't run. I think it is something easy that I am missing, but just can't see.

 

[Joe4]

Right click all the way in the right-corner of the Function Name box, and a button with three dots should pop-up.
Click on that and expand Functions and drill down until your find it.

Or just type: UpdateNulls() in the box (note the parentheses are needed).

 

[knoll126]

Easier to explain with pictures. So I am able to add it in there, I think. I then run the macro, and I get an error. I have run macros before in Excel, for this one I guess I am just lost on seeing if it starts or anything. Again thank you.

(Imgur: The most awesome images on the Internet)

(Imgur: The most awesome images on the Internet)

 

[knoll126]

Second Note. I was able to run the Macro for a little bit, but then it gets through 1300 lines and then comes back with an error at .Update line in the VBA code. The line where is stops still has a unique ID and nothing looks different than any of the other lines that were processed.