• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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
 
0
dlabraham
Asked:
dlabraham
1 Solution
 
shanesuebsahakarnCommented:
Specify which fields you want to append your data to:

INSERT INTO autotest (Field1, Field2, Field4) VALUES (1,'chon','female')
0
 
jadedataMS Access Systems CreatorCommented:
Greetings dlabraham!

INSERT INTO autotest VALUES (clng(1), 'chon',   , 'female');

since autonumbers are long data types, try specifying the datatype.

regards
:)-j-
0
 
JonoBBCommented:
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')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JonoBBCommented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
>> 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.
0
 
GRayLCommented:
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.
0
 
GRayLCommented:
Wished I could Spell!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now