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)

Account Number	Client Name	Product Code
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 Number	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.

[June7]

Criteria for text field type needs apostrophe delimiters:

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

Dates use # and numbers nothing.

[Kash]

Criteria for text field type needs apostrophe delimiters:

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

Dates use # and numbers nothing.

Still getting the same error, maybe its just me and how i am putting the query in. I even made a sample table to double check. Thank you at least I have the format right now.

[June7]

Is [Client Name] really a number field that stores ClientID (as should be) and has a Lookup for the client name?

If so, setting Lookups in tables bites again. I don't set Lookups in tables.

[Kash]

No it is a textfield with the Client name, there is no client id. Does that need an apostrophe also?

[June7]

Oops, Client Name and Account Number have a space, include brackets - "[Client Name]", "Customer", "[Account Number]=". This is why I don't use spaces or special characters in names or reserved words as names.

[Kash]

Oops, Client Name and Account Number have a space, include brackets - "[Client Name]", "Customer", "[Account Number]=". This is why I don't use spaces or special characters in names or reserved words as names.

That's it, you made my day amazing. Thank you very much.