Insert query in Access

Question

I have query build from Visual Studio 2005. I am inserting records from SQL database to Access.

My query is

insert into i_mails
   (id,from_mails,to_mails,cc_mails,subject,body,
    anchor_level_id,attachment,forward_status ,reply_status,delete_status,        read_status, 
   received_date,response_date,
   batch,forward_score,delete_score,priority,
    is_auto_reply,parent_mail_id,case_id,time_bound) 

values (1,'[email protected]',  '[email protected]','[email protected]',  'Hi','--long html field--',
        7 , 'True' ,False,False,False,False,
        #12/12/2000 00:00:00# ,#12/12/2000 00:00:00#,
        0, '0','0',  1 , 
        'False',0,2,0)

I get Data Type Mismatch in criteria expression Error..
Not knowing where this error is firing in Query ... Please Help !!

Access Database Structure:

Id - Number,
from_mails - Memo,
to_mails - Memo,
cc_mails - Memo,
subject - Memo,
body - Memo,
anchor_level_id - Number,
attachment -Yes\No,
forward_status - Yes\No,
reply_status - Yes\No,
delete_status - Yes\No,
read_status - Yes\No,
received_date - DateTime,
response_date - DateTime,
batch - Number,
forward_score - Number, delete_score - Number,
priority - Number,
is_auto_reply - Yes\No,
parent_mail_id - Number,
case_id - Number,
time_bound - Number,

Answer 1

Replace each value with a null (or other 'zero' value). Start with the first, then the second (leaving the first as null) and on until you have replaced all the values with nulls. test the query after each change.

When the query starts working, the last value you changed is the one that is causing the problem.

P.S. It may not be the only value that is incorrect, but using this method you will find all the badly typed values.

Hope this helps

Answer 2

it's hard to tell without knowing the table structure and datatypes but at a complete guess

the values you're trying to insert into received_date & response_date

are incorrect because of the # symbol delimiters...

try changing them to

'12/12/2000 00:00:00' ,'12/12/2000 00:00:00' 
//i.e. Surround them with apostrophes

Answer 3

For is_auto_reply, use 'false' without quotes. This seems to be the problem

Answer 4

It might be worth checking if your '-- long html field --' contains any formatting issues (does it contain any apostrophes, for example, which would be interpreted as prematurely ending the field).

Beyond that, what the others said: without details of the table structure, no ones going to be able to help you much.

Answer 5

Look at data you're trying to insert into fields: attachment and is_auto_reply They should be boolean valued fields while you're trying to insert string data 'True' and 'False'.