By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 1,872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Update Query -Type conversion failure

P: 19
OS: MS XP
Access version: 2003 SP2

I am trying to use an update query to replace quote marks with nothing. In essence, I'm removing quote marks. I get a error message when a field is empty or has no value in it.

Query:
Field: Zipcode
Table: tblMemberInfo
Update to: Replace([Zipcode],"""","")

Error message: Microsoft office access can't update all the records in the update query. Microsoft office access didn't update 2 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 records(s) due to lock violations, and 0 record(s) due to validation rule violations. Do you want to continue running this type of action query anyway? To ignore the error(s) and run the query, click Yes. For an explination of the causes of the violation, click help.

Yes button will run the query fine. Help button brings up nothing.

In the table for the field "zipcode" is set as a text field for using US zipcodes and Canadian postal codes.
The field is not required and Null values allowed is set to yes.

Is there a way around this error message. How do you update a field that has no value.
Apr 12 '07 #1
Share this Question
Share on Google+
2 Replies


pks00
Expert 100+
P: 280
try wrapping field with NZ so as to treat it as a empty string

i.e.

Replace(NZ(Zipcode,""),"""","")


or add a condition on your query, only update where ZipCode IS NOT NULL
Apr 12 '07 #2

P: 19
try wrapping field with NZ so as to treat it as a empty string

i.e.

Replace(NZ(Zipcode,""),"""","")


or add a condition on your query, only update where ZipCode IS NOT NULL
Thanks, that worked. Very much appreciated.
Apr 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.