Code compilation problem

[George-Bowyer]

Hi,

I have the following function in my "General" Module

(It's not code that I wrote myself - and I can't even remember where I got it from).

Public Function CSql( _
ByVal Value As Variant) _
As String

Const vbLongLong As Integer = 20
Const SqlNull As String = "Null"

Dim Sql As String
Dim LongLong As Integer

#If Win32 Then
LongLong = vbLongLong
#End If
#If Win64 Then
LongLong = VBA.vbLongLong
#End If

Select Case VarType(Value)
Case vbEmpty ' 0 Empty (uninitialized).
Sql = SqlNull
Case vbNull ' 1 Null (no valid data).
Sql = SqlNull
Case vbInteger ' 2 Integer.
Sql = Str(Value)
Case vbLong ' 3 Long integer.
Sql = Str(Value)
Case vbSingle ' 4 Single-precision floating-point number.
Sql = Str(Value)
Case vbDouble ' 5 Double-precision floating-point number.
Sql = Str(Value)
Case vbCurrency ' 6 Currency.
Sql = Str(Value)
Case vbDate ' 7 Date.
Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
If DateValue(Value) = Value Then
Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
Else
Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
Case vbString ' 8 String.
Sql = Replace(Trim(Value), "'", "''")
If Sql = "" Then
Sql = SqlNull
Else
Sql = " '" & Sql & "'"
End If
Case vbObject ' 9 Object.
Sql = SqlNull
Case vbError ' 10 Error.
Sql = SqlNull
Case vbBoolean ' 11 Boolean.
Sql = Str(Abs(Value))
Case vbVariant ' 12 Variant (used only with arrays of variants).
Sql = SqlNull
Case vbDataObject ' 13 A data access object.
Sql = SqlNull
Case vbDecimal ' 14 Decimal.
Sql = Str(Value)
Case vbByte ' 17 Byte.
Sql = Str(Value)
Case LongLong ' 20 LongLong integer (Valid on 64-bit platforms only).
Sql = Str(Value)
Case vbUserDefinedType ' 36 Variants that contain user-defined types.
Sql = SqlNull
Case vbArray ' 8192 Array.
Sql = SqlNull
Case Else ' Should not happen.
Sql = SqlNull
End Select

CSql = Trim(Sql)

End Function

On my laptop, using Office365, it compiles fine when I click debug on the module.

However, on my client's system the line <<Dim LongLong As Integer>> throws up a syntax error.

I presume that at one point LongLong was being dim'ed as a long, but I have no idea who changed that or when.

I am using Access from Office365 - the client is using Access 2019

Any tips on how to resolve the compilation problem on the client's setup, please?

 

[Josef P.]

LongLong is a data type in 64 bit VBA.
Dim Long as Integer would also not be possible in a 32 bit VBA.

 

[The_Doc_Man]

It is hard to find a specific list of reserved words, but at least some versions count LONGLONG as a reserved word indicating a data type. If you search the forum for LONGLONG you will see that it pops up when discussing conversions of 32-bit to 64-bit code. Access doesn't like it when you use one of the reserved words as a name in a declaration statement.

It is possible that you could enclose LongLong in brackets - [] - to let Access know that is a name. It might also work if you just changed the name in that one location.

 

[Josef P.]

I would design the code like this:

#If Win64 Then
#Else
Const vbLongLong As Integer = 20
#End If

Public Function CSql( _
   ByVal Value As Variant) _
   As String
...

Select Case VarType(Value)
       Case vbEmpty, vbNull
           Sql = SqlNull
       Case vbInteger, vbLong, vbLongLong, vbSingle,  ....
           Sql = Str(Value)
      ...

 

[George-Bowyer]

I would design the code like this:

#If Win64 Then
#Else
Const vbLongLong As Integer = 20
#End If

Public Function CSql( _
   ByVal Value As Variant) _
   As String
...

Select Case VarType(Value)
       Case vbEmpty, vbNull
           Sql = SqlNull
       Case vbInteger, vbLong, vbLongLong, vbSingle,  ....
           Sql = Str(Value)
      ...

Hmm. Sorry, I'm not sure that I understand your notation.

Do the ellipses mean that I put everything else in exactly as it was before?

Where does the bit with the hashtags go (hashtags are new to me in access VBA, except in the context of dates)?

The annoying thing is I'm not even sure why I put this code in my module, or what it does...

 

[The_Doc_Man]

The annoying thing is I'm not even sure why I put this code in my module, or what it does...

That code LOOKS like it provides a data-type name to an input data-type code number - as would be found in the context of passing in as an argument the result of either TableDef.Fields( n ).Type or QueryDef.Fields( n ).Type as if you were trying to reverse-engineer or translate something in a field description based on SQL-standard type names rather than, say, native Access type names. If you were doing a database documenter in whole or in part, it would make sense.

 

[Josef P.]

CSql from MajP:

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

... to convert values in sql text.

 

[George-Bowyer]

I have changed LONGLONG to myLONGLONG. This seems to have cleared the compile issue.

Thanks to all and MajP.

 

[Josef P.]

Why do you need the variable myLONGLONG?
You could also just write 20 in the case line.

Case 20 ' .. = vbLongLong  ..  LongLong integer (Valid on 64-bit platforms only).
           Sql = Str(Value)

Or work with the compiler instruction #if and define vbLongLong for 32 bit.

' Modul header
Option Compare Text
Option Explicit

#If Win64 Then
#Else
Const vbLongLong As Integer = 20
#End If

'---------------------
' Procedures
Public Function CSql( _
   ByVal Value As Variant) _
   As String

   Const SqlNull       As String = "Null"
   Dim Sql             As String

   Select Case VarType(Value)
       Case vbEmpty            '    0  Empty (uninitialized).

[...]  .. Code from #1 ... cut out so it's not so long


       Case vbByte             '   17  Byte.
           Sql = Str(Value)
       Case vbLongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
           Sql = Str(Value)

[...]

   CSql = Trim(Sql)

End Function

Or work with the compiler instruction #if to include lines in 64 bit VBA and exclude the lines in 32 bit VBA:

   ...
#If Win64 Then
       Case vbLongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
           Sql = Str(Value)
#end if
      ...

The select-case statement could be written with fewer lines (+ comments in code):

Select Case VarType(Value)

       Case vbEmpty, vbNull            '    0  Empty (uninitialized), 1  Null (no valid data).
           Sql = SqlNull

       Case vbInteger, vbLong,vbSingle, vbDouble,  vbCurrency,  vbDecimal,  vbByte  ' 2-6, 14, 17 ... numeric data
           Sql = Str(Value)

#If Win64 Then ' only for 64 bit VBA
       Case vbLongLong ' 20 vbLongLong ... LongLong data type in 64 bit VBA
            Sql = Str(Value)
#End If

       Case vbDate             '    7  Date.
           If DateValue(Value) = Value Then
              Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
           Else
              Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
           End If

       Case vbString           '    8  String.
           Sql = Replace(Trim(Value), "'", "''")
           If Sql = "" Then
               Sql = SqlNull
           Else
               Sql = " '" & Sql & "'"
           End If

      Case vbBoolean          '   11  Boolean.
           Sql = Str(Abs(Value))  ' is this true? ... I think only for T-SQL: bit (true) = 1, Jet/ACE: boolean (true) = -1)
    '     Sql = iif(Value, "True", "False") ... for Jet/ACE and ODBC linked tables
    '     Sql = Str(Abs(Value)) ... for T-SQL

       Case vbObject           '    9  Object.
           Sql = SqlNull  ' Why don't raise an error? ... this will be a code error
       Case vbError            '   10  Error.
           Sql = SqlNull  ' Why don't raise an error
       Case vbVariant          '   12  Variant (used only with arrays of variants). => could you then use an in-statement?
           Sql = SqlNull
       Case vbDataObject       '   13  A data access object.
           Sql = SqlNull ' Why don't raise an error? ... this will be a code error
       Case vbUserDefinedType  '   36  Variants that contain user-defined types.
           Sql = SqlNull   ' Why don't raise an error? ... this will be a code error
       Case vbArray            ' 8192  Array. => could you then use an in-statement?
           Sql = SqlNull
       Case Else               '       Should not happen.
           Sql = SqlNull ' Why don't raise an error? ... this will be a code error

   End Select

I would not simply return "Null" for data types that cannot/should not be converted, but would raise an error. These are errors made by the programmer and not input errors by the user.

Example:
Programmer write:

WhereCondition = "FieldX In (" & CSql(Array("a", "b", "c")) & ")"

=> Result: FieldX in (Null)
Since an array is not supported in CSql, I think an error should be raised indicating that arrays are not handled with CSql.

 

[George-Bowyer]

Wow. Thanks for the detailed reply.

I can't answer your questions. As i think I mentioned above, I don't really understand what the code does or why I put it into my module.

I'm pretty sure it was one of those cases where I had a problem on here (or a forum like this) and someone gave me an answer that involved using this function.

It must have worked at the time, because I don't have that problem any more (whatever it was).

But now I don't know whether it's a function that I used once to do a specific job or one that runs every day as part of my db?