MS Access / SQL : error in insert query statement

Question

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.

Logical is fine, its the insert value that you should look at. — Takarii
– Takarii, Commented Sep 2, 2016 at 12:23
Change the name of the attribute and see if the error goes away. — nicomp
– nicomp, Commented Sep 2, 2016 at 12:23
Hi All, Thanks for your response. I am unable to change database filed and table name . It is a predefined. Kindly Confirm is there any possible we have. Thanks in advance. — user2095503
– user2095503, Commented Sep 2, 2016 at 12:28
How is this query going ? Insert Into Data_CustomerTransmit_Tbl (Logical) Values (1) — Thomas G
– Thomas G, Commented Sep 2, 2016 at 12:57

ThunderFrame · Accepted Answer · 2016-09-05 12:37:52Z

1

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]);

answered Sep 5, 2016 at 12:37

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

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

Comments

D.E. Taylor · Accepted Answer · 2016-11-04 22:59:23Z

0

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 & ")"

answered Nov 4, 2016 at 22:59

D.E. Taylor

12 bronze badges

Comments

Lukas Wiedemann · Accepted Answer · 2017-01-23 14:55:28Z

0

In VBA the code should look like this:

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

answered Jan 23, 2017 at 14:55

Lukas Wiedemann

972 silver badges14 bronze badges

Comments

jsscio · Accepted Answer · 2017-05-04 14:28:01Z

0

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.

answered May 4, 2017 at 14:28

jsscio

291 bronze badge

Comments

CP-NS · Accepted Answer · 2017-08-26 09:41:27Z

0

Use Cbool function:

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

answered Aug 26, 2017 at 9:41

CP-NS

112 bronze badges

Comments

Frank · Accepted Answer · 2018-03-14 14:22:53Z

0

Add single quotes around values?

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

edited Mar 14, 2018 at 14:22

answered Mar 14, 2018 at 13:46

Frank

318 bronze badges

Collectives™ on Stack Overflow

MS Access / SQL : error in insert query statement

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related