Compile Error : Syntax Error - MS ACCESS 2007 on SQL INSERT INTO Statement

Question

I am attempting to create the following SQL INSERT INTO statement, but I am getting a Syntax Error. Any suggestions?:

SQL = "
INSERT INTO AptRent 
    (BuildingID,
     RentAmountID, 
     InitialRentDueDate, 
     TenantID, 
     AptNoID, 
     MoveInDate, 
     MoveOutDate,  
     DateAdded, 
     SecurityAmount, 
     SecurityPaid, 
     NoDaysLate, 
     LateAmount) 
    VALUES
    (" & Me.cbSelectBuildingForRental & "," 
       & Me.cbCreateRentalRentAmount & "," 
       & Me.tbCreateRentalRDD & "," 
       & Me.cbSelectTenantForRental & ", '" 
       & Me.cbSelectAptNoforRental & "' ," 
       & Me.tbCreateRentalMID & "," 
       & Me.tbCreateRentalMOD & "," 
       & Now() & "," 
       & Me.tbSecurityAmnt & "," 
       & Me.ckSecurityPd & "," 
       & Me.tbNoDaysLate & "," 
       & Me.tbLatePmnt & ")"

Table AptRent has the following data types:

BuildingRentID: AutoNumber

BuildingID: Number (Me.cbSelectBuildingForRental)

RentAmountID: Number (Me.cbCreateRentalRentAmount)

InitialRentDueDate: Date/Time (Me.tbCreateRentalRDD)

TenantID: Number (Me.cbSelectTenantForRental)

AptNoID: Text (Me.cbSelectAptNoforRental)

MoveInDate: Date/Time (Me.tbCreateRentalMID)

MoveOutDate: Date/Time (Me.tbCreateRentalMOD)

DateAdded: Date/Time (Now())

SecurityAmount: Currency (Me.tbSecurityAmnt)

SecurityPaid: Yes/No (Me.ckSecurityPd)

NoDaysLate: Number (Me.tbNoDaysLate)

LateAmount: Currency (Me.tbLatePmnt)

Joe Stefanelli · Accepted Answer · 2012-10-19 21:38:45Z

1

Assuming they're ISO formatted (YYYY-MM-DD HH:NN:SS), you need to add single quotes around your Date/Time values.

...
& "'" & Me.tbCreateRentalMID & "', " 
...

and so on for all of your Date/Time columns.

answered Oct 19, 2012 at 21:38

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Over a year ago

Actually, since he's using ms-access. He'll want to use # instead of '.

Joe Stefanelli Over a year ago

@DanielCook: I believe single quotes will work when the value is ISO formatted, which I stated in my assumption. Otherwise, you are correct.

HK1 Over a year ago

I think it depends on what "engine" he's using to run this statement. If it's DAO or ODBC Linked Tables (which is also DAO) then he will need hash signs. I believe ADO also requires hash signs although I'm not perfectly sure on that. In any case, he certainly does need something around the date values.

Collectives™ on Stack Overflow

Compile Error : Syntax Error - MS ACCESS 2007 on SQL INSERT INTO Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related