Syntax yet again

[TeeSee]

A few weeks ago I posted asking for help on syntax for an SQL INSERT
INTO problem I was having. Marshall Barton was good enough to respond
but I couldn't get his response to instantly work.
I sort of got the idea of what Marsh was trying to tell me so I set
out building the "VALUES" portion of the SQL "one field at a time"
using the debug.print SQL as feedback. I finally got it to work and I
posted back to the group that was the case and with the following
questions "Would you please take a couple of minutes to explain what
has happened here. Have I achieved what I should have or have I taken
another wrong turn? Also if you could explain how the quotes are
supposed to be presented properly. Thanks again Marsh. "
Firstly, and most imprtantly, it has bothered me since to think that
I was seen as wasting your time Marsh, by not accepting your code.
Please be assured that was not my intention. Over the years I have
appreciated all responses to my questions. The fact still remains that
I just can't follow this syntax thing. Here is todays example that has
me stumped.

dim strWee as String = """" (Thats four ")
debug.Print strWee produces " (Thats one ")
Can someone pls explain the logic in that one?

I would like to also post the code mentioned initially above again and
request a response 'cos I do want to understand what I'm doing rather
than working with trial & error.
DataPathID is a NUMBER field
Marshs' response to my original post (which I couldn't make work)
strSQL = "INSERT into tblDataPaths (DataPathID, txtCode, " _
& "txtSavedData, txtDataType)" _
& "VALUES(""" & DataPathID & """," & txtCode
_
& ",""" & txtPath & """," & txtType & ")"


My subsequent response (result of trial & error)
strSQL = "INSERT into tblDataPaths (DataPathID, txtCode, " _
& "txtSavedData, txtDataType)" _
& "VALUES(""" & DataPathID & """,""" & txtCode
_
& """,""" & txtPath & """,""" & txtType &
""")"
Many Thanks as always.

 

[Steve Sanford]

dim strWee as String = """" (Thats four ")

debug.Print strWee produces " (Thats one ")
Can someone pls explain the logic in that one?

Certain datatypes need delimiters:

Datatype Delimiter Example
Numbers no delimiters 12345
Date/Time # #10/30/2009#
Text " or ' "Accounting"
or 'Accounting'

If you have a single quote (apostrophe) or double quote within a string, you
need to double up on the delimiter to avoid a syntax error.

So in your example, strWee is a text datatype. Strings (text) needs to be
delimited with quotes.

dim strWee as String = " "

(BTW, the above line will cause an error. )

To have a quote show up withing the string, you need to double up on the
quotes by having "". Add that to the declaration and you get

Dim strWee As String
strWee = """"
(the 4 quotes will eval to a single quote)

Now to your SQL statement. If [DataPathID] is a NUMBER datatype, it doesn't
require delimiters. The other three fields (text datatypes) need delimiters.

I use a different method of writing the SQL statement:

strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ", """ & txtCode
strSQL = strSQL & """, """ & txtPath & """, """ & txtType &""")"

Using the strWee declaration, it would look like:

strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ", " & strWee & txtCode
strSQL = strSQL & strWee & ", " & strWee & txtPath & strWee & ", "
strSQL = strSQL & strWee & txtType & strWee & ")"

Here is the test sub I used
'----------------------------------------
Option Compare Database
Option Explicit

Private Sub Command12_Click()
Dim strWee As String
Dim strSQL As String
Dim txtCode As String
Dim txtPath As String
Dim txtType As String

strWee = """"

'fill variables from form controls
txtCode = Me.strCode
txtPath = Me.txtSavedData
txtType = Me.txtDataType

' using quotes
strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, strCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ", """ & txtCode
strSQL = strSQL & """, """ & txtPath & """, """ & txtType & """)"

Debug.Print strSQL
Debug.Print ' blank line

' using strWee
strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ", " & strWee & txtCode
strSQL = strSQL & strWee & ", " & strWee & txtPath & strWee & ", "
strSQL = strSQL & strWee & txtType & strWee & ")"

Debug.Print strSQL

End Sub
'----------------------------------------


HTH

 

[TeeSee]

dim strWee as String = """"   (Thats four ")
debug.Print strWee produces " (Thats one ")
Can someone pls explain the logic in that one?

Certain datatypes need delimiters:

Datatype          Delimiter           Example
Numbers          no delimiters      12345
Date/Time        #                    #10/30/2009#
Text                 "  or '              "Accounting"    
                          or                'Accounting'

If you have a single quote (apostrophe) or double quote within a string, you
need to double up on the delimiter to avoid a syntax error.

So in your example, strWee is a text datatype. Strings (text) needs to be
delimited with quotes.

  dim strWee as String = "   "

(BTW, the above line will cause an error. )

To have a quote show up withing the string, you need to double up on the
quotes by having "". Add that to the declaration and you get

      Dim strWee As String
      strWee = """"        
        (the 4 quotes will eval to a single quote)

Now to your SQL statement. If [DataPathID] is a NUMBER datatype, it doesn't
require delimiters. The other three fields (text datatypes) need delimiters.

I use a different method of writing the SQL statement:

strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ",  """ & txtCode
strSQL = strSQL & """, """ & txtPath & """, """ & txtType &""")"

Using the strWee declaration, it would look like:

strSQL = "INSERT into tblDataPaths"
strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
strSQL = strSQL & " VALUES(" & DataPathID & ",  " & strWee  & txtCode
strSQL = strSQL & strWee  & ", " & strWee & txtPath & strWee & ", "
strSQL = strSQL & strWee & txtType & strWee & ")"

Here is the test sub I used
'----------------------------------------
Option Compare Database
Option Explicit

Private Sub Command12_Click()
   Dim strWee As String
   Dim strSQL As String
   Dim txtCode As String
   Dim txtPath As String
   Dim txtType As String

   strWee = """"

   'fill variables from form controls
   txtCode = Me.strCode
   txtPath = Me.txtSavedData
   txtType = Me.txtDataType

   ' using quotes
   strSQL = "INSERT into tblDataPaths"
   strSQL = strSQL & " (DataPathID, strCode, txtSavedData, txtDataType)"
   strSQL = strSQL & " VALUES(" & DataPathID & ",  """ & txtCode
   strSQL = strSQL & """, """ & txtPath & """, """ & txtType & """)"

   Debug.Print strSQL
   Debug.Print   ' blank line

   ' using strWee
   strSQL = "INSERT into tblDataPaths"
   strSQL = strSQL & " (DataPathID, txtCode, txtSavedData, txtDataType)"
   strSQL = strSQL & " VALUES(" & DataPathID & ",  " & strWee & txtCode
   strSQL = strSQL & strWee & ", " & strWee & txtPath & strWee & ","
   strSQL = strSQL & strWee & txtType & strWee & ")"

   Debug.Print strSQL

End Sub
'----------------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

A few weeks ago I posted asking for help on syntax for an SQL INSERT
INTO problem I was having. Marshall Barton was good enough to respond
but I couldn't get his response to instantly work.
I sort of got the idea of what Marsh was trying to tell me so I set
out building the "VALUES" portion of the SQL "one field at a time"
using the debug.print SQL as feedback. I finally got it to work and I
posted back to the group that was the case and with the following
questions "Would you please take a couple of minutes to explain what
has happened here. Have I achieved what I should have or have I taken
another wrong turn? Also if you could explain how the quotes are
supposed to be presented properly. Thanks again Marsh. "
Firstly, and most imprtantly,  it has bothered me since to think that
I was seen as wasting your time Marsh, by not accepting your code.
Please be assured that was not my intention. Over the years I have
appreciated all responses to my questions. The fact still remains that
I just can't follow this syntax thing. Here is todays example that has
me stumped.

dim strWee as String = """"   (Thats four ")
debug.Print strWee produces " (Thats one ")
Can someone pls explain the logic in that one?

I would like to also post the code mentioned initially above again and
request a response 'cos I do want to understand what I'm doing rather
than working with trial & error.
DataPathID is a NUMBER field
Marshs' response to my original post (which I couldn't make work)
strSQL = "INSERT into tblDataPaths (DataPathID, txtCode, " _
                        & "txtSavedData, txtDataType)" _
                        & "VALUES(""" & DataPathID & """," & txtCode
_
                        & ",""" & txtPath & """," & txtType & ")"

My subsequent response (result of trial & error)
strSQL = "INSERT into tblDataPaths (DataPathID, txtCode, " _
                        & "txtSavedData, txtDataType)" _
                        & "VALUES(""" & DataPathID & """,""" & txtCode
_
                        & """,""" & txtPath & """,""" & txtType &
""")"
Many Thanks as always.

.- Hide quoted text -

- Show quoted text -

Steve .... A sincere thank you for taking the time to explain that to
me I really appreciate it. As usually happens when I'm involved I have
more questions which I really hope you will help me understand. As
follows

I think I now see how all the quotes are applied and to just reasure
myself I am showing below the VALUES part of the SQL. I have spaced
the quotes out in the way I believe they are applied. Please critique.

I then wanted to compare the strWEE code to the quotes code. It is
shown below. This raised the question .... if strWEE returns a single
" and I substituted a single " for each instance of strWEE would we
have enough quotes in the SQL statement??

Both versions run properly but I'm obviously still missing something.

Sorry I'm so thick but I didn't understand the need for four quotes to
return one.
Dim strWee as string = " " " (three with no spaces returns one).
debug.Print strWee returns "
I actually changed my strWee to three quotes and the code ran.
" VALUES(" & DataPathID & ", " "" & txtCode & "" ", " "" & txtPath
& "" ", " "" & txtType & "" ")"

" VALUES(" & DataPathID & ", " & strWee & txtCode & strWee & ", " &
strWee & txtPath & strWee & ", " & strWee & txtType & strWee & ")"

Thank you again