ERROR: Missing semicolon (;) at end of SQL statement.

Have you tried pasting you SQL directly into a new Access Query in SQL view

I'm wondering that your SQL should be as follows:

INSERT INTO 240 (Phone_Number,Custom) VALUES ('5555555','1') WHERE Phone_Number<> '5555555';

Steve

No, that wouldn't be right

If you are wanting to append a row into a table called '240',  I would think you don't need the WHERE clause at all

Can you explain what you want your query to do?

Steve

I may have it wrong.  I'm very new to SQL.  I don't want repeats of phone numbers in my list, which is why i have that where clause.  Basically, I get a new list of 'do not call' numbers once a month, and I'd like to add new ones to the list.

Stephen is right, the INSERT INTO statement is used in what is known as an "append" query, because it adds a new record to the end of the table. For that reason he is also correct that the WHERE statement is of no value in this particular append query.

It sounds like you are trying to update a record that already exists, but you will need to explain specifically what you are after.

If you simply UPDATE "WHERE '" + number + "' NOT IN (Phone_Number);"  you would put the new number in every record that has a phone number different from "number",

Bob

One thing you could do to ensure that your table doesn't get duplicate numbers added to it, is to set the Indexed property of the Phone_Number field to "Yes (No Duplicates)" in design view of the Access table. I guess that then if you run the query "INSERT INTO 240 (Phone_Number,Custom) VALUES ('5555555','1');" an error will occur. Perhaps you can then handle this error or perhaps you can use a transaction (something I don't know about) instead.

Steve

an error will occur.

should read

an error will occur if the number already exists in the table.

Steve

Here is an sql way of doing the same thing.

You will need to use a unique field in the table ( I have used custID here - just replace it with yours) and you will need to have a Reference set to DAO 3.6
You can call this function from a form button.

Public Function udateit()
    Dim phnumber As String  'your number variable - number is a reserved name and can create confusion when used in code.
    Dim db As Database
    Dim sql As String
    Dim custID As String
    custID = "walmart"      'change this to whatever customer or record id you have that is unique.
    phnumber = 5552222      'change to your phone number.
    Set db = CurrentDb
    sql = "UPDATE brands2 SET brands2.Phone_number = '" & phnumber & "' WHERE (((brands2.Phone_number)not in (select custId,Phone_number from brands2 where Phone_number = '" & phnumber & "' and " & custID & " <> Customer_ID)))"
    db.Execute (sql)

End Function



Bob

You may need to modify the code.  Look for the record with the required Phone_Number, Cutom.  If not there then DoCmd.RunSQL "your querry without the where clase"

The proper string concatination character is & not +.  Trying changing to ampersand and see if it fixes anything.

GRayL: I am using c# not vb
hnaser: I tried that technique.  While it works, it is too slow
Stephen, I will try the the error handling techinque next.  I believe that it too will be slow, because error handling seems to be very slow in .NET.
Bob, I will also try your SQL syntax.  I tried similar SQL code, and i was still getting the semi colon error, but maybe I missed something.

I'll keep you guys posted.

Thanks for all the responses.  If any new ideas pop in your head, keep em comin' ;)

I may have been to quick to say that hnaser's technique was too slow.  I just tried INSERTING, without checking for any previous instances of a phone number in the table, and it is taking a long time.   I had assumed the select AND the insert was taking too long, but the insert seems to be taking the bulk of the time.  After I check Bob's technique, I will post points to the solution that worked best for me.  I then will post another question, "How can I speed up this process!!!" :)

If you index on the phone number and don't allow duplicates ... ??

I did something similar...unfortunately, even INSERTING, even without checking for dupes, is too slow ( a list can contain a few million rows).  To circumvent this, a while back I came up with a solution that used sorted flat files with binary searching; but updating was overly complex.  I guess maybe I should try another db.  Any suggestions?