Placement of Quotes For DoCmd.RunSQL Statement (2 Viewers)

[lhooker]

I'm having trouble with the proper placement of the quotes for a Do.Cmd.RunSQL VBA statement. The SQL code works. Would someone add the DoCmd.RunSQL statement and quotes in the proper place for the below code ? Eventually, "Evans" will become a variable rather being hardcoded. Thanks ! ! !

INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )
SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]
FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals
WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])="Evans"));

 

[moke123]

try:

  strSql =  " INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )  " & _
"SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name] " & _
"FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals "  & _
WHERE [Spiritual_Gift_Totals].[Survey_Taker_First_Name] =""" &  YourVariable & """"

currentdb.execute strsql, dbfailonerror

 

[cheekybuddha]

Dim strSQL As String, strName As String

strName = "Evans"
strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name ) " & _
" VALUES ('" & strName & "'):"
CurrentDb.Execute strSQL, dbFailOnError

 

[cheekybuddha]

Are you really sure you want to do this?

Normally you would just store the related contact's primary key

 

[Josef P.]

Dim SqlString as String
dim FirstName as String

FirstName = "Evans"

SqlString = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )" & _
" SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]" & _
" FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals" & _
" WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])=""" & FirstName & """));"
' Or
SqlString = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )" & _
" SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]" & _
" FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals" & _
" WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])='" & FirstName & "'));"

CurrentDb.Execute SqlString, dbfailonerror

... FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals
Why the cross join?

With a function to build sql string:

Dim SqlString as String
dim FirstName as String

FirstName = "Evans"

SqlString = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )" & _
" SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]" & _
" FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals" & _
" WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])=" & TextToSqlText(FirstName) & "));"

'see: https://github.com/AccessCodeLib/AccessCodeLib/blob/master/data/SqlTools.cls#L457C3
Public Function TextToSqlText(ByVal Value As String) As String
   TextToSqlText =  "'" & Replace$(Value, "'", "''") & "'"
End Function

CurrentDb.Execute SqlString, dbfailonerror

Run as Querydef:

Dim qdf as DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("")
qdf.Sql = "PARAMETERS [ParamFirstName] Text(255);" & _
" INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )" & _
" SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]" & _
" FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals" & _
" WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])=[ParamFirstName]));"

qdf.Parameters("ParamFirstName").Value = "Evans"
qdf.Execute dbfailonerror

 

[lhooker]

try:

  strSql =  " INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )  " & _
"SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name] " & _
"FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals "  & _
WHERE [Spiritual_Gift_Totals].[Survey_Taker_First_Name] =""" &  YourVariable & """"

currentdb.execute strsql, dbfailonerror

This did not work.

 

[lhooker]

Dim strSQL As String, strName As String

strName = "Evans"
strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name ) " & _
" VALUES ('" & strName & "'):"
CurrentDb.Execute strSQL, dbFailOnError

This worked . . . I had to change the colon to semicolon. Thanks ! ! !

 

[moke123]

This did not work.

Missing a " i/f/o where.

Note that single quotes wont work if the name contains an apostrophe. You'll need to double up double quotes if any apostrophes.

 

[Gasman]

I always have a Debug.Print of the sql string before I attempt to use it, and comment out it's use, until I get the concatenation correct.

 

[cheekybuddha]

Missing a " i/f/o where.

Won't work either if there isn't already a record in the table with Survey_Taker_First_Name = 'Evans' !

 

[lhooker]

This worked . . . I had to change the colon to semicolon. Thanks ! ! !

I added a few other fields and now receiving errors (as shown below). Can you see the cause ?


Run-time error '3346';
Number of query values and destination fields are not the same.

Dim strSQL As String, strFormFirstName As String, strFormMiddleName As String, strFormLastName As String, Survey_Taker_Telephone_Number As String

strFormFirstName = [First_Name]
strFormMiddleName = [Middle_Name]
strFormLastName = [Last_Name]
strFormTelephoneNumber = [Telephone_Number]

strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name, Survey_Taker_Middle_Name, Survey_Taker_Last_Name, Survey_Taker_Telephone_Number ) " & _
" VALUES ('" & strFormFirstName & strFormMiddleName & strFormLastName & strFormTelephoneNumber & "');"

CurrentDb.Execute strSQL, dbFailOnError

 

[moke123]

Comment out the currentdb.execute line and add a debug.print before it.

strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name, Survey_Taker_Middle_Name, Survey_Taker_Last_Name, Survey_Taker_Telephone_Number ) " & _
" VALUES ('" & strFormFirstName & strFormMiddleName & strFormLastName & strFormTelephoneNumber & "');"


Debug.Print strSQL

'CurrentDb.Execute strSQL, dbFailOnError

run it and see what it looks like in the immediate window.

Where are you getting the values of [First_Name], etc.

EDIT- Just noticed that you have only 1 set of single quotes. You need to do each value individually and have commas between values.

" VALUES (""" & strFormFirstName &  """,""" &  strFormMiddleName & ""","""  & strFormLastName  & """,""" & strFormTelephoneNumber &  """);"

Like Reply

 

[lhooker]

I added a few other fields and now receiving errors (as shown below). Can you see the cause ?


Run-time error '3346';
Number of query values and destination fields are not the same.

Dim strSQL As String, strFormFirstName As String, strFormMiddleName As String, strFormLastName As String, Survey_Taker_Telephone_Number As String

strFormFirstName = [First_Name]
strFormMiddleName = [Middle_Name]
strFormLastName = [Last_Name]
strFormTelephoneNumber = [Telephone_Number]

strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name, Survey_Taker_Middle_Name, Survey_Taker_Last_Name, Survey_Taker_Telephone_Number ) " & _
" VALUES ('" & strFormFirstName & strFormMiddleName & strFormLastName & strFormTelephoneNumber & "');"

CurrentDb.Execute strSQL, dbFailOnError

That was it . . . . It's working now . . . . Thanks again ! ! !

 

[lhooker]

That was it . . . . It's working now . . . . Thanks again ! ! !

I thought that the "Where" statement was working, it's not. What is wrong ?.

Compile error.
Sub or Function not defined

strSQL = "INSERT INTO Individual_Spiritual_Gift_Totals (Survey_Taker_First_Name, Survey_Taker_Middle_Name, Survey_Taker_Last_Name, Survey_Taker_Telephone_Number) " & _
" VALUES (""" & strFormFirstName & """,""" & strFormMiddleName & """,""" & strFormLastName & """,""" & strFormTelephoneNumber & """);"
WHERE [Spiritual_Gift_Totals].[Survey_Taker_First_Name] = """" & strFormFirstName & """"""

Also, I need to add the below variables to the "Where" statement.

strFormMiddleName
strFormLastName
strFormTelephoneNumber

I appreciate your help ! ! !

 

[cheekybuddha]

INSERT adds new records. There is no WHERE involved.

Are you sure you don't want an UPDATE ?

 

[The_Doc_Man]

You may be confused about INSERT INTO. It has two forms. The VALUE form does not have a WHERE clause. You CAN, however, have a WHERE clause in the INSERT INTO ... SELECT ... FROM ... WHERE version of the query.

 

[moke123]

Are you sure you don't want an update query?
oops, should have hit post a while ago

 

[lhooker]

INSERT adds new records. There is no WHERE involved.

Are you sure you don't want an UPDATE ?

Oh man, I been working with the wrong piece of code. I've been foucused on the quotes and forgot
about the "Where" statement. Initially, I had a "Where statement in my example (with a hard coded name). My example worked
in a MS Access query. If can just get the below code to work with the following variables, it wiil
be greatly appreciated.

INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )
SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]
FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals
WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])="Evans"));

Varibles
strFormFirstName
strFormMiddleName
strFormLastName
strFormTelephoneNumber

This whole process retrieves data from a form textbox into above variables, use the variables for a criteria to retrieves records
from the "Spiritual_Gift_Totals" table and adds the record to the "Individual_Spiritual_Gift_Totals" table. The
"Individual_Spiritual_Gift_Totals" table is input to a report. This may not be the best solution, but the only one that I feel
comfortable with. I'm open to a better solution.

 

[cheekybuddha]

I've been foucused on the quotes and forgot about the "Where" statement.

You may still be confused!

What exactly are you trying to do?

Are you trying to add a new duplicate record, copying the data from the existing record where Survey_Taker_First_Name = 'Evans' ?

Or are you trying to update the existing record where Survey_Taker_First_Name = 'Evans' with new values for FirstName, MiddleName, LastName and TelephoneNumber?

Or are you trying to do something else?

INSERT INTO Individual_Spiritual_Gift_Totals ( Survey_Taker_First_Name )
SELECT [Spiritual_Gift_Totals].[Survey_Taker_First_Name]
FROM Individual_Spiritual_Gift_Totals, Spiritual_Gift_Totals
WHERE ((([Spiritual_Gift_Totals].[Survey_Taker_First_Name])="Evans"));

The above query doesn't make much sense, unless you are specifically trying to to insert new records in to your table.

If you have more than one existing record where Survey_Taker_First_Name = 'Evans' then you will insert the same number of new records. ie, if you had five existing records in table Spiritual_Gift_Totals then the statement will add five new records using this query.

I doubt that's what you intend! (though let us know if it is what you intended!)

Also, there is no real utility to using the INSERT ... SELECT ... syntax since the new record's Survey_Taker_First_Name will be 'Evans' as well. You may as well specify it directly using the INSERT ... VALUES ... syntax.

But all this leads me to suspect you are actually trying to update an existing record.

Please explain in words what you are trying to achieve.

 

[moke123]

This whole process retrieves data from a form textbox

If this code is within a forms module you can use the ME keyword along with the name of the control. Intellisense will kick in an make writing it easier and without spelling errors.

" VALUES (""" & Me.FirstName &  """,""" &  Me.MiddleName & ""","""  & Me.LastName  & """,""" & Me.TelephoneNumber &  """);"

... use the variables for a criteria to retrieves records
from the "Spiritual_Gift_Totals" table and adds the record to the "Individual_Spiritual_Gift_Totals" table. The
"Individual_Spiritual_Gift_Totals" table is input to a report.

Are "Spiritual_Gift_Totals" and "Individual_Spiritual_Gift_Totals" similiar or exact copies of each other?
Are you splitting this out just for the report? You probably don't need to if your data is set up correctly.

I've been foucused on the quotes

Getting delimiters right can sometimes be difficult in complicated code. It often helps to have "Helper" functions like the one Josef P. has in post 5.

I sometimes use the following function. I have a public enum because it it used in a couple procedures.

Public Enum eDelimiterType
    NoDelimiter = 0
    DoubleQuotes = 1
    Octothorpes = 2
    SingleQuotes = 3
End Enum

Public Function Dlmt(objIN As Variant, Optional Delimiter As eDelimiterType = 1) As Variant
'returns the passed in value wrapped with the selected delimiter

    On Error GoTo Dlmt_Error
   
    Dim DeLimit As String

    Select Case Delimiter
        Case 0
            DeLimit = Null
        Case 1
            DeLimit = Chr(34) 'Quotes
        Case 2
            DeLimit = Chr(35) 'Octothorpes
        Case 3
            DeLimit = Chr(39) 'SingleQuotes
    End Select
               
    Dlmt = DeLimit & objIN & DeLimit
   
    On Error GoTo 0
    Exit Function

Dlmt_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Dlmt, line " & Erl & "."

End Function

Using that function the code would look like

" VALUES (" & Dlmt(Me.FirstName, DoubleQuotes) & "," & Dlmt(Me.MiddleName, DoubleQuotes) & "," & Dlmt(Me.LastName, DoubleQuotes) & "," & Dlmt(Me.TelephoneNumber, DoubleQuotes) & ");"