MS Access / SQL : error in insert query statement

6
votes

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.

6
sqlvbams-access
Logical is fine, its the insert value that you should look at. - Takarii
Change the name of the attribute and see if the error goes away. - nicomp
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
How is this query going ? Insert Into Data_CustomerTransmit_Tbl (Logical) Values (1) - Thomas G
Access actually uses -1 for true, not 1. - SunKnight0

6 Answers

1
votes

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

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

In VBA the code should look like this:

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

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.

0
votes

Use Cbool function:

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

Add single quotes around values?

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