dlabraham
asked on
Syntax for Inserting a record with an autonumber field
I am trying to insert a record into a table that has four fields and I do not know the syntax for inserting a record with the an autonumbered field.
Table name - Autotest
Field 1 - id, Number data type
FIeld 2 - name, Text data type
Field 3 - id2, Autonumber data type
Field 4 - description, Text data type
The SQL statement I am trying to run is:
INSERT INTO autotest
VALUES (1, 'chon', , 'female');
I've tried ## in the space for the autonumber and several others but I get a data type mismathc when running it.
Please advise on the proper SQL syntax for MS Access.
Thanks,
Chon
Table name - Autotest
Field 1 - id, Number data type
FIeld 2 - name, Text data type
Field 3 - id2, Autonumber data type
Field 4 - description, Text data type
The SQL statement I am trying to run is:
INSERT INTO autotest
VALUES (1, 'chon', , 'female');
I've tried ## in the space for the autonumber and several others but I get a data type mismathc when running it.
Please advise on the proper SQL syntax for MS Access.
Thanks,
Chon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You cant tell an autonumber field what value you want to put in it, hence the name 'autonumber'
If the first field is an autonumber field, then you dont reference that field.
Try:
INSERT INTO Autotest (name, ID2, description) VALUES ('chon', , 'female')
If the first field is an autonumber field, then you dont reference that field.
Try:
INSERT INTO Autotest (name, ID2, description) VALUES ('chon', , 'female')
Oh, and this assumes that your ID2 field can accept null values - make sure that your field definition has been set accordingly, otherwise access will spit the dummy
>> You cant tell an autonumber field what value you want to put in it, hence the name 'autonumber'
Yes you can insert a Long Integer value into an AutoNumber field ... although there's no reason to do so.
Yes you can insert a Long Integer value into an AutoNumber field ... although there's no reason to do so.
As Shane implied above, do not reference the autonumer field.
INSERT INTO autotest (Field1, Field2, Field4) VALUES (1,'chon','female').
When the record is added, the autonumber datatype will create the value for that field.
INSERT INTO autotest (Field1, Field2, Field4) VALUES (1,'chon','female').
When the record is added, the autonumber datatype will create the value for that field.
Wished I could Spell!
INSERT INTO autotest VALUES (clng(1), 'chon', , 'female');
since autonumbers are long data types, try specifying the datatype.
regards
:)-j-