Type Mismatch Error Driving Me Nuts

[joshyanny]

I'm trying to run the command:
INSERT
INTO dbo_Contact_Addr
(
id, addr, addr2, city, state, country, zipcode,
zip_extnd
)
SELECT id, address, address2, city, state, country, zipcode,
zipcode_extnd
FROM Contact_Addr

To move data from an Access database into a SQL Server 2005 database.

The datatypes are as follows:
dbo_Contact_Addr.
id SMALLINT
addr VARCHAR(50)
addr2 VARCHAR(50)
city VARCHAR(50)
state VARCHAR(50)
country VARCHAR(50)
zipcode SMALLINT
zip_extnd SMALLINT

Contact_Addr.
id AUTONUMBER
address TEXT
address2 TEXT
city TEXT
state TEXT
country TEXT
zipcode NUMBER
zipcode_extnd NUMBER

I've tried using the conversion wrapper functions (CInt, CStr, etc)
and gotten nowhere. It keeps giving me the error:
Runtime Error '3464' : Data Type Mismatch In Criteria Expression

Any ideas?

 

[Jeff Boyce]

Why is zipcode a NUMBER? You aren't going to be adding or subtracting
zipcodes, are you? (I'm guessing you don't have any non-US entries in that
table, as UK 'postal codes' include alpha characters).

What happens if you use 'text' as a type for the "zipcode"-related fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

It's been a while since I've used SQL Server, but I thought Accesses
LongInteger (autonumber) data type mapped to SQL Servers Integer data type.
If this is the case, it would probably affect your ID, ZipCode and Zip_Extnd
fields in SQL Server.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[fredg]

I'm trying to run the command:
INSERT
INTO dbo_Contact_Addr
(
id, addr, addr2, city, state, country, zipcode,
zip_extnd
)
SELECT id, address, address2, city, state, country, zipcode,
zipcode_extnd
FROM Contact_Addr

To move data from an Access database into a SQL Server 2005 database.

The datatypes are as follows:
dbo_Contact_Addr.
id SMALLINT
addr VARCHAR(50)
addr2 VARCHAR(50)
city VARCHAR(50)
state VARCHAR(50)
country VARCHAR(50)
zipcode SMALLINT
zip_extnd SMALLINT

Contact_Addr.
id AUTONUMBER
address TEXT
address2 TEXT
city TEXT
state TEXT
country TEXT
zipcode NUMBER
zipcode_extnd NUMBER

I've tried using the conversion wrapper functions (CInt, CStr, etc)
and gotten nowhere. It keeps giving me the error:
Runtime Error '3464' : Data Type Mismatch In Criteria Expression

Any ideas?

I don't work with SQL Server, but at the very least an Access
AutoNumber field is a Long Integer datatype, which you're trying to
insert into a SQL Server SmallInt field.

Also, your Zipcode and Zipcode_extnd fields are Number datatypes.
They should be text.
The maximum size of a SmallInt field is +32767.
Zip codes can run up to 99999.
Also, a Number datatype will not save any preceding zeroes, and some
Zip codes and Extensions do begin with a Zero, i.e. 00501.
If you do change the datatype to Text, you'll need to also change the
datatype in the SQL Server table.