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.

Musick

Logical is fine, its the insert value that you should look at. – Sympathizer

Change the name of the attribute and see if the error goes away. – Everetteeverglade

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. – Musick

How is this query going ? Insert Into Data_CustomerTransmit_Tbl (Logical) Values (1) – Colonnade

Yes this query need to insert one field only. Table name - Data_CustomerTransmit_Tbl, Field name - Logical. Datatype - boolean. Value- True. – Musick

Access actually uses -1 for true, not 1. – Rixdollar

Why this string expression {" & Logicalnme & "} for Boolean field. – Quadrilateral

Try with Values (" & Logicalname & "). No one would label a variable Logicalnme ... – Scotney

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

Manganese

Answer 2

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

Necromancy

Answer 3

In VBA the code should look like this:

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

Chart

Answer 4

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.

Tabulate

Answer 5

Use Cbool function:

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

Loganiaceous

Answer 6

Add single quotes around values?

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

Ampoule