CODEWITHSUNDEEP
sqlvbams-access
user2095503
user2095503

Reputation: 87

MS Access / SQL : error in insert query statement

I am using MS Access 1997 version (.mdb file). On a daily basis, I need to insert values manually. In that file, there's a column Logical (Boolean data type). I am automate this template using SQL query instead of direct entry.

Below is my insert query:

Insert Into Data_CustomerTransmit_Tbl (Logical) 
Values (" & Logicalnme & ")

Values:

Logicalnme - True

When I run this query in VBA in Excel, I get this error message

Syntax Error in Insert into Statement

Kindly confirm shall I use "Logical" as column name or this is the reserved keyword?

Thanks in advance.

Upvotes: 6

Views: 1751

Answers (6)

Frank
Frank

Reputation: 31

Add single quotes around values?

sql = "INSERT INTO Data_CustomerTransmit_Tbl (Logical) " & _  
      "VALUES ('" & Logicalnme & "')"
docmd.runsql sql

Upvotes: 0

CP-NS
CP-NS

Reputation: 11

Use Cbool function:

Insert Into Data_CustomerTransmit_Tbl (Logical) 
Values (" & Cbool(Logicalnme) & ")

Upvotes: 0

jsscio
jsscio

Reputation: 29

The SQL query you alluded to - have you tried to execute it manually in the query editor using the same value(s) you are trying to pass from Excel? That would immediately provide more verbose feedback if there is an issue with the query or the data.

Regarding the Boolean field, make sure you are receiving a True/False that you are expecting, not a bit field, 0 and 1. I like to make quick log entries in a table or a file when troubleshooting to see the raw data.

Upvotes: 0

Lukas Wiedemann
Lukas Wiedemann

Reputation: 97

In VBA the code should look like this:

Docmd.RunSQL("INSERT INTO Data_CustomerTransmit_Tbl (Logical) VALUES (" & Logicalnme & ");"

Upvotes: 0

D.E. Taylor
D.E. Taylor

Reputation: 1

I presume you are trying to do this insert using VBA? If so, your syntax in building the SQL statement is correct, except you have some punctuation missing: double-quotes on each end.

    "INSERT INTO Data_CustomerTransmit_Tbl (Logical) VALUES (" & Logicalnme & ")"

Further, as you have split the string over two lines (breaking before VALUES), you must also terminate the first line of the string with: ' " & _' (space,double-quote,space, ampersand, space, underscore) in order to indicate that the string continues to the next line. Then you begin the next line with double-quotes:

    "INSERT INTO Data_CustomerTransmit_Tbl (Logical) " & _  
    "VALUES (" & Logicalnme & ")"

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9471

There isn't a problem with your field name, you just need to enclose your INSERT column name in square brackets. You also need to choose a valid value in the VALUES clause:

INSERT INTO Data_CustomerTransmit_Tbl ( [Logical] )
VALUES (TRUE);

If you want to be prompted for the value to insert, you can use a parameter:

PARAMETERS [Please enter a Boolean value] YesNo;
INSERT INTO Data_CustomerTransmit_Tbl ( [Logical] )
VALUES ([Please enter a Boolean value]);

Upvotes: 1

Related Questions