Solved StringFormatSQL from Northwind (2 Viewers)

[ClaraBarton]

When a variable is a string, the following adds quotes to it:

strSql = StringFormatSQL("UPDATE tblItems" & _
                                " SET ITPath={0}," & _
                                " Item={1}," & _
                                " InUse={2}" & _
                                " WHERE {3};", _
                                strITPath, "(New)", True, strCriteria)

It looks like this:
UPDATE tblItems SET ITPath ='1 < 2', Item ='(New)', InUse =True WHERE 'flocID = 7 And DocNo = 1';
This is the formula:

Public Function StringFormatSQL(ByVal s As String, ParamArray params() As Variant) As String

I can remove the quotes from (New) but how do I handle {0} and {3}?

 

[tvanstiphout]

You would use StringFormat, and handle the value decorations yourself.
Or you would use StringFormatSQL, and handle arguments 0 and 3 yourself.

 

[cheekybuddha]

I can remove the quotes from (New) but how do I handle {0} and {3}?

Why are you removing the quotes from {0} and '(New)'? ITPath and Item appear to be string datatypes, no?

For {3} You need to build the SQL more explicitly (use {3} and {4}).

strSql = StringFormatSQL("UPDATE tblItems" & _
                                " SET ITPath = {0}," & _
                                " Item = {1}," & _
                                " InUse = {2}" & _
                                " WHERE flocID = {3}" & _
                                "   AND DocNo = {4};", _
                                strITPath, "(New)", True, 7, 1)

 

[ClaraBarton]

Ah! I KNEW you'd know! Thanks!
So Here's my update :

strSql = StringFormatSQL("UPDATE tblItems" & _
                                " SET " & StringFormat("ITPath = {0}", strITPath) & "," & _
                                " Item = " & "(New)" & "," & _
                                " InUse = True" & _
                                " WHERE flocID = {0} and" & _
                                " DocNo = {1};", _
                                ID, lDocNo)

Which Returns:

UPDATE tblItems SET ITPath = 1 > 3, Item = (New), InUse = True WHERE flocID = 7 and DocNo = 1;

Have I gained anything? Actually probably going to be bald from pulling my hair... but it's exactly what I need.
Thank you.

 

[tvanstiphout]

> UPDATE tblItems SET ITPath = 1 > 3
Are you SURE that's what you want?
This evaluates to:
UPDATE tblItems SET ITPath = False