[AccessD] Handling strings in VBA SQL scripts?

There's no-harm/no-foul to have extra brackets as a saftey measure.
Combined with the Replace function (A2k+) ... I would just do:

"SELECT '" & replace(strTableName, "'","''") & "' as tablename " & _ 
"FROM [" & strTableName & "]"

hth
Steve


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Harry Coenen
Sent: Tuesday, April 20, 2004 9:42 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Handling strings in VBA SQL scripts?


Hi All

I am searching for a function that determines the need for barcketing
and need for apostrophing/stringing of variable names. For example a
tablename stored in variable strTableName with value "table1" would not
need bracketing, while value "table 1" definitly needs bracketing, e.g.
as part of an SQL string "FROM [" & strTableName & "]".  

To make it a bit more difficult the value could also be "John's Table".
This would cause problems when we need to string the variable in an SQL
string, e.g. "SELECT '" & strTableName & "' AS TableName, " would result
in "SELECT 'Peter's table' AS FromTable," which obviously is not
correct, it should be "SELECT 'Peter''s table' AS FromTable,".  It would
also cause problems when we decide to code using something like
rst(strTableName).

Up until now I either avoided the problem by forcing users to use
unproblematic names, or developed one of solutions dependent on the data
(hoping that they would not change their naming conventions.

Thanks to Skrol29 (http://www.skrol29.com/) and Juan M. Afán deRibera
(http://www.mvp-access.com/juanmafan/) I am aware of
WizHook.IsValidIdent and WizHook.BracketString for testing valid
variable names.  But Wizhook is an undocumented feature.  I am also
aware of tools which "string" a string for you, e.g. CodeCrafter or the
MS VBA String Editor that can do this with a static string during design
time. But I was wondering if someone has found the time to find a more
generic solution.

The following gives my proto-analysis:

Function OutputString (strInputString as String,
                      Optional intFlag as Integer = 0,
                      Optional strStringChar As String = '"') AS string
' ============================ ' Arguments: ' strInputString : the
string to be converted
' intFlag        : the type of conversion
'                   0 = standard string conversion converting
apostrophes
'                   1 = just bracketing for filename
'                   and others that could be useful
' strStringChar  : the character to be used as start and close of the
string

'                  standard "
'============================
' PRELIMINARY ANALYSIS
' If intFlag = 1 Then
'   check if bracketing is needed
'   check for a the appearance of a list of characters in the string
(which
characters?)
'   if one appears, at least bracket
' Else
'   if apostrophe appears double it
' Endif
End Function

Further note: Probably the function should return a Boolean indicating
success or failure of the action while the result is transfered to the
caller by other means. How?

Many thanks in advance

Harry Coenen

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com