Solved Sql VBA syntax

[ClaraBarton]

"INSERT INTO tblLabel (Line1, Line2, Line3, Line4) " & _
        "VALUES " & _
        "(""" & Company & """, " & _
        """" & FirstName & " " & "" & LastName & """, " & _
        """" & Address & """, " & _
        """" & City & " " & "" & State & " " & "" & ZIP & """)"

I am so bad at this... I want to insert a field called Sort here and I cannot figure out how to insert the literal "A"

"INSERT INTO tblLabel (Sort, Line1, Line2, Line3, Line4) " & _
        "VALUES " & _
        "(""" & Company & """, " & _
        """" & FirstName & " " & "" & LastName & """, " & _
        """" & Address & """, " & _
        """" & City & " " & "" & State & " " & "" & ZIP & """)"

The first code works great.

 

[Edgar_]

Maybe

"INSERT INTO tblLabel (Sort, Line1, Line2, Line3, Line4) " & _
        "VALUES " & _
        "(""" & "A" & """, " & _
        """"& Company & """, " & _
        """" & FirstName & " " & "" & LastName & """, " & _
        """" & Address & """, " & _
        """" & City & " " & "" & State & " " & "" & ZIP & """)"

I didn't see your "A" for your Sort column.

 

[ClaraBarton]

I took it out because I couldn't get the syntax to work. I want it in the first field

 

[ClaraBarton]

You gorgeous person you! I have spent too too much time on it. Thank you so much.

 

[Edgar_]

Glad it helped you.

 

[ebs17]

I wonder why such a syntax would be used to create a record.
Two alternative methods:
1) Recordset

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("tblLabel", dbOpenDynaset)
With rs
   .AddNew
   !Sort = "A"
   !Line1 = Company
   !Line2 = FirstName & " " & LastName
   !Line3 = Address
   !Line4 = City & " " & State & " " & ZIP
   .Update
   .Close
End With

2) Real parameter query

PARAMETERS
   parSort Text(255),
   parLine1 Text(255),
   parLine2 Text(255),
   parLine3 Text(255),
   parLine4 Text(255)
;
INSERT INTO
   tblLabel(
      Sort,
      Line1,
      Line2,
      Line3,
      Line4
   ) VALUES(
   parSort,
   parLine1,
   parLine2,
   parLine3,
   parLine4
)

This then executes the query and transfers the parameters via VBA.

ExecuteParamQuery look at https://www.access-programmers.co.uk/forums/threads/sql-code-does-nothing.327088/#post-1871559

Call:

ExecuteParamQuery CurrentDb, "NameQuery", _
      "parSort", "A", _
      "parLine1", Company,
      "parLine2", FirstName & " " & LastName,
      "parLine3", Address,
      "parLine4", City & " " & State & " " & ZIP

 

[ClaraBarton]

I use the recordset from a form with multiple records. The SQL is just for one record. Got it from Richard Ross. Once I get it to work, I don't mess with it.

 

[ebs17]

Once I get it to work

Creating records in tables is a basic and repetitive task for a database developer. You should just master it completely and do it, not experiment and waste time.
Developing an application is often a marathon. It's problematic when you find yourself running the gauntlet even when it comes to basic and simple tasks.

Simplified and, in my opinion, much clearer version of your experiment above:

sSQL = "INSERT INTO tblLabel (Sort, Line1, Line2, Line3, Line4) " & _
       "VALUES ('A', '" & Company & "', '" & _
       FirstName & " " & LastName & "', '" & _
       Address & "', '" & _
       City & " " & State & " " & ZIP & "')"
Debug.Print sSQL

 

[cheekybuddha]

Simplified and, in my opinion, much clearer version

I agree.

I never understand why folks insist on using escaped double quotes as the string delimiter in SQL strings constructed in VBA.

It becomes virtually impossible to distinguish which characters are part of the SQL and which are VBA.

Often the argument is given that substitute variables may contain a single quote which will cause the SQL to fail, but they could as easily contain a double quote too

And IMHO escaping/handling single quotes is clearer than escaping every single double quote.

 

[Josef P.]

Based on #8 on the topic of readability, one more suggestion:

Public Function BuildInsertAddressLabelSql(ByVal SortOfLabel As String, _
                           ByVal Company As String, _
                           ByVal FirstName As String, ByVal LastName As String, _
                           ByVal Address As String, _
                           ByVal City As String, ByVal State As String, ByVal Zip As String)

     BuildInsertAddressLabelSql = BuildInsertLabelSql(SortOfLabel, _
                                       Company, _
                                       StringFormat("{0} {1}", FirstName, LastName), _
                                       Address, _
                                       StringFormat("{0} {1} {2}", City, State, Zip))


End Function

Public Function BuildInsertLabelSql(ByVal SortOfLabel As String, _
                           ByVal Line1 As String, _
                           ByVal Line2 As String, _
                           ByVal Line3 As String, _
                           ByVal Line4 As String)


   Const InsertSqlTemplate As String = _
         "INSERT INTO tblLabel (Sort, Line1, Line2, Line3, Line4)" & _
         " VALUES ({0}, {1}, {2}, {3}, {4})"
  
   BuildInsertLabelSql = StringFormatSQL(InsertSqlTemplate, SortOfLabel, Line1, Line2, Line3, Line4)

End Function

Note: StringFormat and StringFormatSQL: see Northwind 2

 

[Gasman]

If you have never seen Northwind 2, that is confusing as hell?

 

[Josef P.]

If you have never seen Northwind 2, that is confusing as hell?

If that's confusing then it's time to have a look at Northwind 2.

 

[Gasman]

With 2007, that is not possible, however I have got the drift of that syntax. Not sure of the benefit of repeating the parameters TBH though, but I know there should be a good reason?

 

[Josef P.]

You mean because of the 2 functions BuildInsertAddressLabelSql and BuildInsertLabelSql?
The idea was just to create a general function to fill the 4-line label SQL, which can then be used for "special" label functions.
Of course, you don't have to divide it up like this, but it could be practical.

Following the SRP (Single Responsibility Principle):
BuildInsertAddressLabelSql ... generates the line content
BuildInsertLabelSql ... defines the general insert statement for the lines
StringFormatSQL .. takes care of inserting the values correctly

 

[CJ_London]

And IMHO escaping/handling single quotes is clearer than escaping every single double quote.

agree

 

[Gasman]

You mean because of the 2 functions BuildInsertAddressLabelSql and BuildInsertLabelSql?
The idea was just to create a general function to fill the 4-line label SQL, which can then be used for "special" label functions.
Of course, you don't have to divide it up like this, but it could be practical.

Following the SRP (Single Responsibility Principle):
BuildInsertAddressLabelSql ... generates the line content
BuildInsertLabelSql ... defines the general insert statement for the lines
StringFormatSQL .. takes care of inserting the values correctly

No, by the fact that you appear to pass in 0 and 1 and then the actual value for 0 and 1, or as many as are needed.

 

[Josef P.]

I see. StringFormat and StringFormatSQL reproduce the basic concept of String.Format (C#).

Interface of StringFormat (NW2): Public Function StringFormat(ByVal s As String, ParamArray params() As Variant) As String
{0}..{n} ... are placeholders for the params() values.

Note: now it would be paractical if the code of NW 2 was in GitHub (or published elsewhere), then I could refer to the function.

 

[Edgar_]

You should just master it completely and do it, not experiment and waste time.

I think the OP should stick with what feels right for them. It's not okay to make people doubt themselves. If the code works, leave it alone, that's the standard practice anyway. Shall it create trouble later, change it later. Besides, you can GPT that thing into any variation you prefer in seconds, if necessity comes.

 

[ebs17]

@Edgar_
So you think that you shouldn't really know and master what you're doing, but should rather ask "higher intelligences" - and that about things that are needed in everyday life in your own work?
It's probably a good feeling to deny your own intelligence or hand it over to the gatekeeper.

Do you also have an opinion on the suggestions I made (GPTless)?

 

[Edgar_]

Do you also have an opinion on the suggestions I made (GPTless)?

I think your suggestions are good alternatives to avoid dealing with string manipulation, but OP made a perfectly valid and sound point. If the code works, it works, next thing. My point is to address changes when they are necessary, instead of coming up with the perfect solution according to Eberhard. Keep in mind that OP may already have a coding style established across the app, accomodating your style there just because you said it's the correct way and, on top of that, reading that their experimentation is inadequate is not the thing one wants to deal with when learning.

As for the GPT part of my comment, which seems to be a problem for you, you should embrace it for what it is, a tool, or not. That thing keeps growing anyway.