>>>Sorry to mislead everyone. I actually cut short the SQL statement to
>>>make it looks simple, but I have left out the 2 parenthesis.
>>>My actual statement is as follows:
>>>
>>>cnn1.Execute "INSERT INTO DTCList(IndexNo, NRIC, CustName, DTCDate, Birthday, PrintDate) " & _
>>>"VALUES('" & Trim(!IndexNo) & "', '" & Trim(!NRIC) & "', '" & Trim(!CustName) & "', #" & Trim(CStr(!DTCDate)) & "# , #" & Trim(CStr(!Birthday)) & "# , #" & Trim(CStr(Date)) & "# ) " & _
>>>"WHERE DTCList.IndexNo <> '" & Trim(!IndexNo) & "' AND DTCList.CaseClosed"
>>>
>>>I still get the missing semicolon(;) error, pls advise, thanks a lot.
>>
>>Wait a minute! Why do you have a Where clause in an Insert Statement?
>
>The WHERE clause is the conditions that I would like to check before
>writing a record into the Table. If the conditions are met, then would
>I want a record to be inserted. Or any other ways to handle such case?
>Pls provide your expertise advise, thanks.
In your query, you use the bang operator (!). This means to me that you have an open recordset.
What do you want to do? Do you have 2 tables and you want to copy from one to the other records that are not "CaseClosed" and are <> from a number?
In this case, you could this something like this:
cnn1.Execute "INSERT INTO DTCList(IndexNo, NRIC, CustName, DTCDate, Birthday, PrintDate) " & _
"SELECT IndexNo, NRIC, CustName, DTCDate, Birthday, Date() " & _
"FROM TableFromWhichYouWantToCopy " &
"WHERE IndexNo <> '" & Trim(!IndexNo) & "' AND CaseClosed"
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com