FixedFixUp (SQL humiliation)

Question

Is there somewhere a function which will take care of delimiting SQL statements ?

Answer

Roger,

Here is FixedFixUp based on the FixUp function from "Microsoft Access 2.0 How-To CD" (by Getz, Feddema, Gunderloy, and Haught) (see P.S.). Don't hesitate to make it FixedFixedFixUp or SuperFixedFixup ;-)

HTH,
Shamil

P.S. The code:

Function FixedFixUp(ByVal varValue As Variant) As Variant

        'Add the appropriate delimiters, depending on the data type.
        'Put quotes around text, "#" around dates, and nothing
        'around numeric values.

        Dim strQuote As String

        ' strQuote contains the ANSI representation of
        ' a quote character
        strQuote = Chr$(34)

        Select Case VarType(varValue)
                Case V_INTEGER, V_SINGLE, V_DOUBLE, V_LONG, V_CURRENCY
                        FixedFixUp = smsNoCommasAsDecDelimiter(varValue)
                Case V_STRING
                        FixedFixUp = strQuote & varValue & strQuote
                Case V_DATE
                        FixedFixUp = "#" & Format(varValue, "mm") & "/" _
                                    & Format(varValue, "dd") & "/" _
                                    & Format(varValue, "yyyy") & "#"
                Case Else
                        FixedFixUp = Null
        End Select

End Function

'*+
'
'  Function smsNoCommasAsDecDelimiter (pvar As Variant)
'
'  Returns a string representing <pvar> number with a point as a decimal
separator.
'  (MS Access SQL scripts should have point symbol (".") as a decimal
separator !!!
'   If you build SQL scripts in Access Basic you have to supply them
'      with the numbers in this format. )
'
'*-
Function smsNoCommasAsDecDelimiter(pvar As Variant)
   On Error GoTo smsNoCommasAsDecDelimiter_err
   Dim strActualDecSeparator As String * 1
   Dim intDecSeparatorPos As Integer
   Dim strRet As String

   strActualDecSeparator = Mid(CStr(9.9), 2, 1)

   strRet = CStr(pvar)
   intDecSeparatorPos = InStr(1, strRet, strActualDecSeparator)
   If intDecSeparatorPos > 0 Then
      strRet = Left(strRet, intDecSeparatorPos - 1) & _
          "." & _
          Mid(strRet, intDecSeparatorPos + 1)
   End If
   smsNoCommasAsDecDelimiter = strRet
smsNoCommasAsDecDelimiter_Done:
   Exit Function
smsNoCommasAsDecDelimiter_err:
   Resume smsNoCommasAsDecDelimiter_Done
End Function