Solved StringFormatSQL from Northwind

[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

 

[ClaraBarton]

Aargh! "1 > 3" ! and "(New)" ! Well... getting closer

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

Returns:

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

 

[Josef P.]

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

That should also be the result of the suggestion in post #3 (only ' instead of ").

But let's start in reverse order: What should the result look like?
Is that the target:
UPDATE tblItems SET ITPath = '1 > 3', Item = '(New)', InUse = True WHERE flocID = 7 and DocNo = 1;

If I interpret your posts correctly, InUse = True and Item = ‘(New)’ are fixed values. These can remain directly in the string template.

dim SqlTemplate as String
SqlTemplate = "UPDATE tblItems SET ITPath = {0}, Item = '(New)', InUse = True WHERE flocID = {1} and DocNo = {2}"
strSql = StringFormatSQL(SqlTemplate, strITPath, ID, lDocNo)

 

[cheekybuddha]

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

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

The two statements above are exactly the same, except using the single quotes prevents with having to futz with your triple double quotes when building your SQL string, and is handled automatically by the StringFormatSQL() function - as you discovered in the beginning!

(And hence why I was asking about your trying to remove the quotes in Post #3)

 

[ClaraBarton]

Now I've done a complete circle and am back where I started.

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

Returns:

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

Thank you for all your help!

 

[cheekybuddha]

Returns:
UPDATE tblItems SET ITPath = '1 > 3', Item = "(New)", InUse = True WHERE flocID = 7 and DocNo = 1;Thank you for all your help!

You're welcome!

(I'm guessing that you took the above 'Returns' code from here and hand-edited it to resemble what is output by the function because you still have double quotes around (New) !)

 

[ClaraBarton]

Be sure your sins will find you out!

UPDATE tblItems SET ITPath = '1 > 11 > 2', Item = '(New)', InUse = True WHERE flocID = 12 and DocNo = 1;