Strange error on doing a replace on a table with code

[Southern at Heart]

I have this code that does a replace on my table tblContacts. It works fine
right after I've imported data from Outlook into this table. But the
strangest thing is, if I open the table and delete all the contents of one
cell, any cell, then this code gives me the error "..did not update all the
records due to 1 type conversion error...
I can type something back into that cell, and it'll work!
Why is this???


'REMOVE THE & SIGN FROM ALMOST ALL FIELDS
strSql = "Update tblContacts Set tblContacts.[HomeAddressStreet] = " &
vbCrLf & _
"Replace([HomeAddressStreet],""&"",""and""), tblContacts.[FirstName] = " &
vbCrLf & _
"Replace([FirstName],""&"",""and""), tblContacts.[LastName] = " & vbCrLf & _
"Replace([LastName],""&"",""and""), tblContacts.[HomeAddressCity] = " &
vbCrLf & _
"Replace([HomeAddressCity],""&"",""and""), tblContacts.[HomeAddressState] =
" & vbCrLf & _
"Replace([HomeAddressState],""&"",""and"")"
DoCmd.RunSQL (strSql)

thanks again.
Southern@Heart

 

[Klatuu]

Check the fields in your table to see if they allow Nulls. From your
description of the problem, I would expect Nulls are not allowed.

 

[Southern at Heart]

Under 'allow zero length' it is 'yes' for all of the fields.
I don't see anything about 'null' it that the same thing?
I don't get any error when I open the table, delete the contents of a cell...
Only when I run this replace code.
So strange.

Check the fields in your table to see if they allow Nulls. From your
description of the problem, I would expect Nulls are not allowed.
--
Dave Hargis, Microsoft Access MVP

I have this code that does a replace on my table tblContacts. It works fine
right after I've imported data from Outlook into this table. But the
strangest thing is, if I open the table and delete all the contents of one
cell, any cell, then this code gives me the error "..did not update all the
records due to 1 type conversion error...
I can type something back into that cell, and it'll work!
Why is this???


'REMOVE THE & SIGN FROM ALMOST ALL FIELDS
strSql = "Update tblContacts Set tblContacts.[HomeAddressStreet] = " &
vbCrLf & _
"Replace([HomeAddressStreet],""&"",""and""), tblContacts.[FirstName] = " &
vbCrLf & _
"Replace([FirstName],""&"",""and""), tblContacts.[LastName] = " & vbCrLf & _
"Replace([LastName],""&"",""and""), tblContacts.[HomeAddressCity] = " &
vbCrLf & _
"Replace([HomeAddressCity],""&"",""and""), tblContacts.[HomeAddressState] =
" & vbCrLf & _
"Replace([HomeAddressState],""&"",""and"")"
DoCmd.RunSQL (strSql)

thanks again.
Southern@Heart

 

[Klatuu]

I just reread you original post.
Take all the vbCrLf's out of your string. First, Jet doesn't understand
Access contstants and second, they are not needed. Also, I don't know how
you constructed the query, but if all you are trying to do is clear the
fields, you might try it like this:

strSql = "Update tblContacts Set [HomeAddressStreet] = Null,
[HomeAddressStreet] = Null, [FirstName] = Null, [FirstName] = Null,
[LastName] = Null, [HomeAddressCity] = Null, [HomeAddressState] = Null;"

--
Dave Hargis, Microsoft Access MVP

Under 'allow zero length' it is 'yes' for all of the fields.
I don't see anything about 'null' it that the same thing?
I don't get any error when I open the table, delete the contents of a cell...
Only when I run this replace code.
So strange.

Check the fields in your table to see if they allow Nulls. From your
description of the problem, I would expect Nulls are not allowed.
--
Dave Hargis, Microsoft Access MVP

I have this code that does a replace on my table tblContacts. It works fine
right after I've imported data from Outlook into this table. But the
strangest thing is, if I open the table and delete all the contents of one
cell, any cell, then this code gives me the error "..did not update all the
records due to 1 type conversion error...
I can type something back into that cell, and it'll work!
Why is this???


'REMOVE THE & SIGN FROM ALMOST ALL FIELDS
strSql = "Update tblContacts Set tblContacts.[HomeAddressStreet] = " &
vbCrLf & _
"Replace([HomeAddressStreet],""&"",""and""), tblContacts.[FirstName] = " &
vbCrLf & _
"Replace([FirstName],""&"",""and""), tblContacts.[LastName] = " & vbCrLf & _
"Replace([LastName],""&"",""and""), tblContacts.[HomeAddressCity] = " &
vbCrLf & _
"Replace([HomeAddressCity],""&"",""and""), tblContacts.[HomeAddressState] =
" & vbCrLf & _
"Replace([HomeAddressState],""&"",""and"")"
DoCmd.RunSQL (strSql)

thanks again.
Southern@Heart

 

[Southern at Heart]

I took the vbcrlf out. That didn't help, and no, I'm not just trying to
delete the cell data, take another look at my code, which I've pasted below.
To repeate myself:
This code runs fine, just after I've deleted all the data in the table and
imported fresh data from Outlook. The code runs, replaces the & sign with
the word 'and'. But when I open the table and manually select a cell, say an
address, and delete it (leave the cell empty), then close the table, and run
the code again, I get the error, can't update 1 record due to type conversion
failure. If I go back and type something in that cell, anything, even 1
letter, then the code works fine.
Anyone have any idea why this is happening or is it just a glitch in Access?
thanks for your help
Southern@Heart

'REMOVE THE & SIGN FROM ALMOST ALL FIELDS
strSql = "Update tblContacts Set tblContacts.[HomeAddressStreet] = " & _
"Replace([HomeAddressStreet],""&"",""and""), tblContacts.[FirstName] = " & _
"Replace([FirstName],""&"",""and""), tblContacts.[LastName] = " & _
"Replace([LastName],""&"",""and""), tblContacts.[HomeAddressCity] = " & _
"Replace([HomeAddressCity],""&"",""and""), tblContacts.[HomeAddressState] =
" & _
"Replace([HomeAddressState],""&"",""and"")"
DoCmd.RunSQL (strSql)

 

[Klatuu]

Thanks, but I read the code the first time.
I don't know what is happening because I don't know the data types of your
fields.
BTW, there are no cells in Access. They are fields. Cells belong to Excel.

Check the data types of your fields to be sure you are not trying to put a
data type in a field that it can't accept.

 

[Southern at Heart]

All my fields are set to text fields. All my code is trying to do is replace
the & sign with the word 'and', so that should work it seems to me.
Anyway, I split my code up into the following, and it works, now:

''REMOVE THE & SIGN FROM tblContacts FIELDS
'Street
strSql = "UPDATE tblContacts SET tblContacts.HomeAddressStreet =
Replace([HomeAddressStreet],""&"",""and"") " & vbCrLf & _
"WHERE (((tblContacts.HomeAddressStreet) Like ""*[&]*""));"
DoCmd.RunSQL (strSql)
'City
strSql = "UPDATE tblContacts SET tblContacts.HomeAddressCity =
Replace([HomeAddressCity],""&"",""and"") " & vbCrLf & _
"WHERE (((tblContacts.HomeAddressCity) Like ""*[&]*""));"
DoCmd.RunSQL (strSql)
'State
strSql = "UPDATE tblContacts SET tblContacts.HomeAddressState =
Replace([HomeAddressState],""&"",""and"") " & vbCrLf & _
"WHERE (((tblContacts.HomeAddressState) Like ""*[&]*""));"
DoCmd.RunSQL (strSql)
'FirstName
strSql = "UPDATE tblContacts SET tblContacts.FirstName =
Replace([FirstName],""&"",""and"") " & vbCrLf & _
"WHERE (((tblContacts.FirstName) Like ""*[&]*""));"
DoCmd.RunSQL (strSql)
'LastName
strSql = "UPDATE tblContacts SET tblContacts.LastName =
Replace([LastName],""&"",""and"") " & vbCrLf & _
"WHERE (((tblContacts.LastName) Like ""*[&]*""));"
DoCmd.RunSQL (strSql)

Thanks.

 

[Klatuu]

Glad you got it working.