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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Ship_To_City[/TD]
[TD]Ship_To_State[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Seattle[/TD]
[TD]WA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]San Francisco[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Joe4]

Well, the Syntax Error is that you have a comma at the end of your line before your WHERE clause. You need to remove that.
That gets rid of your error, but the code does not seem to do what you want. I suspect it may be because in order for this to work properly, the data needs to be updated in a specific sequential order, and not all at once.

You may be better off using VBA and DAO.Recordsets to loop through the data in order, and update it based on the previous record.

 

[knoll126]

Well, the Syntax Error is that you have a comma at the end of your line before your WHERE clause. You need to remove that.
That gets rid of your error, but the code does not seem to do what you want. I suspect it may be because in order for this to work properly, the data needs to be updated in a specific sequential order, and not all at once.

You may be better off using VBA and DAO.Recordsets to loop through the data in order, and update it based on the previous record.

Hi, thanks for your help.

If it is easier I would like to keep it to a query if not VBA is an option as well. If I split it up into two different queries or even just getting the state would allow me to manipulate the rest of the data in Excel. It is just the data is so big that I can't bring this into Excel. I need to export one specific state and after that it would be feasible to do the rest in Excel. But again I prefer to do this all in Access.

Here is the code then for replacing Nulls with the previous non null cell.

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

When running this query, after removing the comma that you spotted, I get this error "Compile error. in query expression 'DLookUp("[Ship_To_State]","MonthlySalesTax","[ID] = [ID]-1"'.

With my novice background on queries I don't know what the error is. Only thing I can think of based off of other people online using this same expression and saying it work from them is if my spelling is incorrect.

Thank you again.

 

[Joe4]

The thing about Access, is that it is unlike in Excel. Order does not really seem to matter in Access, because records are not related to one another within a table.
Somebody once said to view an Access table as a "bag of marbles", where all the records and random dispersed throughout the table.

Both your queries ran for me, though they did not do anything. That is where order and precedence matter. If it is trying to update row 3 from row 2, before row 2 gets updated, it won't work.

Is your ID field numeric? How is it populated? Is it an Autonumber field?
Note that if it has any "gaps" in it, your logic (DLOOKUP) will not work. For instance, if it goes from 4 to 6, record 6 is going to try to update based on record 5, which does not exist. That could be causing errors.

 

[knoll126]

It is auto populated into Access when I imported the text file. I am assuming it is numeric field if it was auto populated?

EDIT: Looking in Access it is a AutoNumber field. The Ship_To_State is a Short Text Field.

 

[Joe4]

Autonumber can be a tricky thing. While they usually go in order, there is no guarantee that it will do so.
Check to see if there are any gaps in the number. If your sort by ID, and look at the last record, does the record number match the total number of records in the table?

 

[knoll126]

LAst ID number is 3220739 and at the bottom of Access it says Record 3220739 of 3220739. Looks like it is the last one. Another note after saving the query and double clicking it under the Queries on the right hand side it will bring back the query in table view, but with no results. Though if I try and run the query through SQL view and hitting Run through that way I get the compile error.

 

[Joe4]

When you say "but with no results", do you mean that it is showing a bunch of blank records, or it is showing no records?
If it is showing no records, then I suspect that the fields you are checking are not really NULL (NULL and blank are not necessarily the same in Access).

Regardless, I still think you may have issues due to the order that things get updated in (in which you have no control over using an Update Query).

 

[knoll126]

It goes all the way to record 2493853, but has no visible data in the field. If this way isn't able to work due to an update query not being the best, do you have a recommendation. I will say that I am some what of a novice and will need more help in doing it a different way. Thanks.

 

[Joe4]

How I would do it is with a DAO Recordset in VBA. Essentially, you sort the records and then loop through the list record-by-record, updating the values of the blank records with the values from the previous record. The nice thing about that is you don't have to worry about any gaps in the ID numbers, since it is looking at the previous record, and not at any calculated ID number.

If you are interested in this method, let me know. It has been a while since I have done it this way, so I would have to look up the exact details. I used to use this method on lot when we did a huge data conversion.