Eric, thank you very much for your help and you are really good. Thank you.


>>>>>>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"
>>>

>>
>>I have a Recordset opened on the Form, which I will print out each
>>record to a dot matrix printer, record by record, till EOF.
>>
>>After each record has printed, I want to write a transfer the data from
>>the Recordset to a Table(called DTCList in this case).
>>But before I write into the Table, there are some conditions.
>>The IndexNo must not exist AND the CaseClose field is "True"
>>
>>Eg:
>>IndexNo CaseClosed
>>-------------------------
>>A001 False
>>
>>If "A001" exist and CaseClosed field is = "False", then shouldn't write into the
>>Table.
>>But if "A001" exist, and CaseClose field is = "True", then will write
>>into the Table. Pls advise, thanks alot. I am really lost.
>
>In this case, you need query your DTCList to get your condition outside the INSERT query much like this (pseudo-code).
>
>

>Do Until .EOF
>   rs2.open "SELECT * FROM DTCLIST WHERE IndexNo = '" & Trim(!IndexNo) & "' AND CaseClosed"
>   if rs2.recordcount <> 0 then
>      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)) & "# ) "
>   end if
>loop
>