Update Query for blanks getting type conversion failure.

[Kash]

I basically have been thrown into fixing some databases and am relatively new to access. I feel like I am close to resolving this issue but any help is appreciated.

I have a table in access that has some blank fields I am trying to resolve. Here is how it looks like.


Account Client Name Product Code (these are all listed at text type fields)
A100 John X
A100 D
A101 Debbie S
A102 David D
A102 X

What I want to do is make fill in the data with update query so the table looks like this.

Account Client Name Product Code
A100 John X
A100 John D
A101 Debbie S
A102 David D
A102 David X


So I made a query like this

Nz([Client Name], DLookUp("Client Name","Customer","Account Number=" & [Account Number] & " AND Not [Client Name] Is Null"))

and in the update query section I have set the query to is Null. Now it tells me there is about 4000 records like this would I like to update but when I hit yes I get this error.

Microsoft Access didn't update due to a type conversion failure with 0 violations.

So I figure it is a syntax issue but even after the changes I made I still get the same error. So any guidance is appreciated here.

 

[TexanInParis]

Kash-

Try this:

UPDATE Customer
SET [Client Name] = DLookUp("Client Name","Customer","Account Number= '" & [Account Number] & "' AND Len([Client Name] & '') > 0")
WHERE Len([Client Name] & "") = 0;

It might be the case that some of your Client Name field contain a zero length string rather than a null. Testing the Len of the field concatenated with an empty string for 0 covers both the case where the field contains Null or an empty string. Note also that you need quotes around the comparison to Account Number because that is clearly a string, not a number.