Help with Docmd.runsql please.

[Dynamo]

Hi,
I am trying to insert a record into a table based on inputted info from a form
using the code builder. Code is as follows:

Dim Sql As Text
Dim TT As Text

TT = IIF([Text1]="Prefault","Yes","No")
SQL = "INSERT INTO MyTable ([Answer]) VALUES (TT)"
DoCmd.Runsql SQL

The above code does not work. When it is executed I get an input box asking me
to insert a parameter for TT. I have tried replacing TT with [TT]and 'TT' and
get the same result. I have also tried replacing TT with "TT" which actually
inserts the text "TT" into the table.

I know that the variable TT has been assigned the value Yes or No because I have
tested it using another function. So where am I going wrong. What Syntax should
I be using in the SQL statement.

TIA
Dynamo

 

[Douglas J. Steele]

Since you want the value of TT, you need to put it outside of the quotes.

SQL = "INSERT INTO MyTable ([Answer]) VALUES (" & TT & ")"

I'm assuming that Answer is a boolean field. To be honest, I'm not sure if
that will work with values of Yes and No: you may need True and False (yes,
I realize that Access calls boolean fields "Yes/No" fields):

TT = IIF([Text1]="Prefault","True","False")

On the other hand, if Answer is a text field, then you need to put quotes
around the value that you're trying to insert:

SQL = "INSERT INTO MyTable ([Answer]) VALUES ('" & TT & "')"

Exagerated for clarity, that's

SQL = "INSERT INTO MyTable ([Answer]) VALUES ( ' " & TT & " ' )"

 

[Baz]

Dim Sql As Text
Dim TT As Text

TT = IIF([Text1]="Prefault","Yes","No")
SQL = "INSERT INTO MyTable ([Answer]) VALUES (""" & TT & """)"
CurrentDb.Execute SQL, dbFailOnError