Not all strings are equal (1 Viewer)

[tvanstiphout]

(cross-posted in Access MVP email list)

Environment: A365-32.

Some variables have a VarType of vbString (8) and I can pass them to a function that takes a Byte array as argument, and some have the same VarType value and trying to pass those gets a compile error: Type mismatch: array or user-defined type expected.

What explains this difference? Apparently VarType does not confer enough information about the finer points of the String variable, e.g. whether it is convertible to a Byte array or not.

In the attached example I have a table with an AutoNumber that is a Replication ID. We know this is a 16-byte number typically written as a GUID for human consumption.
We’re trying to dump the bytes of such value to the Immediate window.
See procedure testReplicationId.

Dumping the Ascii characters (more accurately, the Unicode characters) can be shown to dump the correct 2-byte values. E.g. the first value is -622 Decimal which is equal to FD92 Hex (the first 2 bytes from DumpBytes) minus 2^16. So far so good.

As you can see from the commented-out line:
'DumpBytes s
I cannot pass that string to the ByRef b() As Byte argument of DumpBytes. A compile error would occur.
Now run testReplicationId, and observer that when I print the VarType of string s, or the DLookup result, or the StringFromGUID result, each reports vbString.
However, only the DLookup and the StringFromGUID result can be passed to DumpBytes.

My questions are:

1. Why is that?
2. Is there some way to distinguish between these strings?

Thanks,

-Tom.

 

[The_Doc_Man]

I cannot pass that string to the ByRef b() As Byte argument of DumpBytes. A compile error would occur.

I am not in a position to download and examine your included file at the moment - I'm getting interrupted by numerous phone calls from family making holiday preparations.

Just to clarify... do you mean a RUN-TIME error would occur? Because to say a compile-time error would occur based on string content (something that is normally only known at run-time) seems to violate the barrier between run-time and compile-time operations. I'm not doubting you, but this distinction is worth careful verification and I want to assure this isn't a misunderstanding due to language issues.

When I look in the VBA language reference (v20140424), I see a several string declarations. First, a general clarification: If the data type is any type that uses the word STRING as part of the definition, that includes UNICODE which specifically includes UTF-16 encoding (when you enable UNICODE operations) as well as 7-bit ASCII when that is your setting.

The defined data types are:

1) Scalar STRING (variable-length string) data type.
2) STRING * n (fixed-size string) data type
3) Fixed size array where the members of the array are a specific data type such as BYTE, DOUBLE, or STRING.
4) Fixed size array where the members are of type VARIANT
5) Resizable arrays of any data type EXCEPT VARIANT
6) A User Defined Type that could contain one or more of the above types.
7) Scalar VARIANT data type that could hold any one of the STRING types list above.

Can you clarify which of the above you mean?

I have not found a newer language reference for VBA so if A365 has a string-capable data type other than the ones I listed, I wouldn't know about its behavior.

 

[DaveMD]

I have observed these same anomalies with VBA strings. One thing that I have found is that there can be a difference how a "Variant/String" is treated compared to how a pure "String" is treated in certain contexts. Interestingly, the VBA debugger can differentiate between the two as shown in the Type column (see below). However, I have found no way to distinguish the two programmatically.

With that said, as far as conversion of anything that can be coerced to a String type to a byte array, you can add a very simple helper function (StrToBytes) and it will work in all cases I have encountered. This should work as a drop-in helper into your code where you shouldn't have to make a differentiation of the inner-workings of the given input variable.

' Function for explicit conversion of anything that can coerce to a string to a byte array
Function StrToBytes(ByVal s As String) As Byte()
    StrToBytes = s
End Function

Sub testReplicationId()
    Dim s As String
    
    s = DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")
    
    ' DumpBytes s    'Type mismatch: array or user-defined type expected.
    DumpBytes StrToBytes(s)    'Works with explicit pre-conversion to byte array with helper
    DumpBytes StrToBytes(DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'"))
    DumpBytes StrToBytes(DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='bbb'"))
    DumpBytes StrToBytes(CStr(DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")))   
    DumpBytes StrToBytes(Application.StringFromGUID(s))
End Sub

 

[Edgar_]

I'm not really sure about the whole point of this exercise, please forgive me for that.

Anyway, watching the replication id field from a recordset returns this:
{guid {4384FD92-A138-4F05-A2F7-129B6F3C49E7}}

And when I open the table, the datasheetview form used to visualize the data shows the textbox has this value:

As for why the DumpBytes isn't working, here's another thing I got quickly experimenting, I don't know what value it has:

Sub skdlfj()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblAccessReplicationId")
    DumpBytes rs.Fields("myReplicationId").Value
End Sub

 0             123          7B            {
 1             0            0             
 2             103          67            g
 3             0            0             
 4             117          75            u
 5             0            0             
 6             105          69            i
 7             0            0             
 8             100          64            d
 9             0            0             
 10            32           20             
 11            0            0             
 12            123          7B            {
 13            0            0             
 14            52           34            4
 15            0            0             
 16            51           33            3
 17            0            0             
 18            56           38            8
 19            0            0             
 20            52           34            4
 21            0            0             
 22            70           46            F
 23            0            0             
 24            68           44            D
 25            0            0             
 26            57           39            9
 27            0            0             
 28            50           32            2
 29            0            0             
 30            45           2D            -
 31            0            0             
 32            65           41            A
 33            0            0             
 34            49           31            1
 35            0            0             
 36            51           33            3
 37            0            0             
 38            56           38            8
 39            0            0             
 40            45           2D            -
 41            0            0             
 42            52           34            4
 43            0            0             
 44            70           46            F
 45            0            0             
 46            48           30            0
 47            0            0             
 48            53           35            5
 49            0            0             
 50            45           2D            -
 51            0            0             
 52            65           41            A
 53            0            0             
 54            50           32            2
 55            0            0             
 56            70           46            F
 57            0            0             
 58            55           37            7
 59            0            0             
 60            45           2D            -
 61            0            0             
 62            49           31            1
 63            0            0             
 64            50           32            2
 65            0            0             
 66            57           39            9
 67            0            0             
 68            66           42            B
 69            0            0             
 70            54           36            6
 71            0            0             
 72            70           46            F
 73            0            0             
 74            51           33            3
 75            0            0             
 76            67           43            C
 77            0            0             
 78            52           34            4
 79            0            0             
 80            57           39            9
 81            0            0             
 82            69           45            E
 83            0            0             
 84            55           37            7
 85            0            0             
 86            125          7D            }
 87            0            0             
 88            125          7D            }
 89            0            0

I see the {guid {4384FD92-A138-4F05-A2F7-129B6F3C49E7}} output vertically there, but I had to modify the DumpBytes routine from the default 20 to 255.

If I don't specify I want the value from that field, DumpBytes returns the same error.

Again, I don't know if there's any value in this reply, I was just playing and its goal is probably beyond my understanding right now.

 

[Josef P.]

Is it a string problem or rather a quirk of DLookup?

Dim s As String
s = DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")
Debug.Print s, Len(s) ' => ????????       8
' Note: is 8 the len of the first hex block of guid?
' fix return a string:
' s = DLookup("StringFromGuid(myReplicationId)", "tblAccessReplicationId", "myShortText='aaa'")

' vs. DAO
With CurrentDb.OpenRecordset("select myReplicationId from tblAccessReplicationId where myShortText='aaa'")
  s = .Fields(0).Value
  ' note: implicit conversion to string .. field data type is dbGUID (15)
  .close
End With
Debug.Print s, Len(s) ' => {guid {4384FD92-A138-4F05-A2F7-129B6F3C49E7}}            45


'vs. ADODB
With CurrentProject.Connection.Execute("select myReplicationId from tblAccessReplicationId where myShortText='aaa'")
  s = .Fields(0).Value
  .Close
End With
Debug.Print s, Len(s) ' => {4384FD92-A138-4F05-A2F7-129B6F3C49E7}     38

/edit:
Inserting the values from DLookup into an array works.

Dim g() As Byte
g = DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")
DumpBytes g
DumpBytesInGuidOrder g[CODE]

[CODE]Private Sub DumpBytesInGuidOrder(ByRef b() As Byte)
   
    Dim i                   As Integer

    For i = 3 To 0 Step -1
      Debug.Print Right("00" & Hex(b(i)), 2);
    Next
    Debug.Print "-";
    For i = 5 To 4 Step -1
      Debug.Print Right("00" & Hex(b(i)), 2);
    Next
    Debug.Print "-";
    For i = 7 To 6 Step -1
      Debug.Print Right("00" & Hex(b(i)), 2);
    Next
    Debug.Print "-";
    For i = 8 To 9
      Debug.Print Right("00" & Hex(b(i)), 2);
    Next
    Debug.Print "-";
    For i = 10 To 15
      Debug.Print Right("00" & Hex(b(i)), 2);
    Next
    Debug.Print
   
End Sub

 

[tvanstiphout]

I am not in a position to download and examine your included file at the moment

I'll wait until you are able to. You will see the compile error when you uncomment that line.
All 3 forms of the variable return a vartype of 8: vbString. That is the point I want to discuss because some of them can be converted to byte array, and some cannot.

 

[tvanstiphout]

I have observed these same anomalies with VBA strings. One thing that I have found is that there can be a difference how a "Variant/String" is treated compared to how a pure "String" is treated in certain contexts.

Yes, I am aware there is a big difference between a String and a Variant of subtype String. The latter would not have a VarType of vbString.

Indeed a conversion function like you showed may be the best idea. What I was after with my question is more understanding of WHY this is so, not how to work around it.
I have not tried it yet, but I would think that if you hook up a debugger and inspect the actual memory locations of the string for each of the 3 examples, they would be different. Maybe the assignment of "s = DLookup(...)" or "s = StringFromGuid(...)" does a conversion to a VBA string (internally called a BSTR I believe).

 

[tvanstiphout]

Anyway, watching the replication id field from a recordset returns this:
{guid {4384FD92-A138-4F05-A2F7-129B6F3C49E7}}

Playing is good! That's how I learn.
You could have called "DumpBytes ..., 0" to indicate unlimited size.
Your approach reveals something interesting: in a recordset the returned value is the string representation of the GUID, as-usual in Unicode so every other byte is a zero. It probably makes sense, since we don't have a native data type for GUID (we have Long Long for 64-bit, but not Long Long Long Long ). GuidFromString can then be used to get the bytes back.

 

[tvanstiphout]

Is it a string problem or rather a quirk of DLookup?
Dim s As String
s = DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")

Inserting the values from DLookup into an array works.

Hi Josef,
I think assigning the result of DLookup to a string somehow modifies it because this fails:
s = DLookup("myReplicationId", "tblAccessReplicationId", "myShortText='aaa'")
DumpBytes s

while this works:
DumpBytes DLookup(...)

I'm tempted to see if I can hook up a debugger to the msaccess.exe process and get to those memory locations. Never done anything like that.