Differences in String Function Operations

The memory storage formats for text are different in Visual Basic for Microsoft Access and Access Basic of previous versions of Microsoft Access. Text is stored in ANSI format within Access Basic code, and in Unicode format in Visual Basic.

The Unicode format is used in Visual Basic to match the format of text within OLE, which is indirectly related to Visual Basic.

For example, the text string "ABC" would be stored in memory as shown below.

Storage format	Storage pattern	Description
Unicode	41 00 42 00 43 00 42 30 44 30 46 30	Each character is stored as 2 bytes.
ANSI	41 42 43 82 A0 82 A2 82 A4	ASCII characters are stored as 1 byte; double-byte characters are stored as 2 bytes.

Because of these differences in internal format, there are string processing functions that operate differently in Access Basic and Visual Basic. The functions that operate differently and their statements are as shown below.

Asc function, Chr function, InputB function, InStrB function, LeftB function, LenB function, RightB function, MidB function, and their corresponding statements.

Also, the ChrB function and AscB function have been added to Visual Basic.

In that these functions and statements both process text in byte units, they are the same in Access Basic and Visual Basic, but because their storage formats for text are different, they operate differently. For example, in LenB("A") would be 1 in Access Basic, but 2 in Visual Basic.

Programs created in previous versions of Microsoft Access that use the string processing functions that work in byte units must be changed in Visual Basic to a source code that recognizes Unicode. However, if only string processing functions that process character units, such as the Len function, Left function, and Right function, are used, there is no need to recognize them.

If programs created in a previous version of Microsoft Access are moved to the current version of Microsoft Access, consider the following points regarding string processing.

Asc Function and AscB Function

This program ran properly in previous versions of Access, but produces a run-time error in the current version of Visual Basic in Microsoft Access.

Print Asc(MidB("", 2,1))

This is because MidB("", 2,1), an argument of the Asc function, does not correctly return data to Unicode text.

Use the following AscB function to make this program run in the current version Microsoft Access:

Print AscB(MidB("", 2,1))

In this program, the value (&H30) of the second Unicode byte is returned.

Chr Function and ChrB Function

The Chr function in Microsoft Access always returns 2-byte characters. In previous versions of Microsoft Access, Chr(&H41) and ChrB(&H41) were equal, but in the current version of Microsoft Access, Chr(&H41) and ChrB(&H41) + ChrB(0) are equal.

Also, in previous versions of Microsoft Access, "" was expressed as ChrB(&H82) + ChrB(&HA0), but in the current version of Microsoft Access it is expressed as ChrB(&H42) + ChrB(&H30).

Calling the Windows Application Programming Interface (API)

In several Windows API the byte length of a string has a special meaning. For example, the following program returns a folder set up in Windows. In Microsoft Access, LeftB(Buffer, ret) does not return the correct string. This is because, in spite of the fact that it shows the byte length of an ANSI string, the LeftB function processes Unicode strings. In this case, use the InStr function so that only the character string, without nulls, is returned.

Private Declare Function GetWindowsDirectory Lib "kernel32" _
    Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, _
    ByVal nSize As Long) As Long

Private Sub Command1_Click()
    Buffer$ = Space(255)
    ret = GetWindowsDirectory(Buffer$, 255)
    ' WinDir = LeftB(Buffer, ret)   '<--- Incorrect code"

    WinDir = Left(Buffer$, InStr(Buffer$, Chr(0)) - 1)
                                        '<--Correct code"
    Print WinDir
End Sub

Input Function and InputB Function

The Input function in Microsoft Access converts the number of characters designated when the text is read from the file into a Unicode string and reads them as variables. The InputB function, on the other hand, assumes the data to be binary data and stores it as variables without converting it. If the InputB function is used when reading a file stored in a fixed length field, the fixed byte length data must be converted once it is read.

Open "Data.Dat" For Input As 1
dat1 = StrConv(InputB(10, 1), vbUnicode)
dat2 = StrConv(InputB(10, 1), vbUnicode)
dat3 = StrConv(InputB(10, 1), vbUnicode)

===DATA.DAT
123456789012345678901234567
Name      Address      Telephone

Processing ANSI string bytes in Microsoft Access 7.0

If it is necessary to process ANSI string bytes in Microsoft Access, use the StrConv function. You can convert text between ANSI and Unicode by setting the vbUnicode or vbFromUnicode constant. If you process bytes after temporarily converting a string to an ANSI string, and then reconvert it back to Unicode once the process is finished, you can use codes from previous version of Access relatively easily.

'  ANSI 
dat = StrConv(dat, vbFromUnicode)
.
.
.    ' 
.    ' 
.
.
'   Unicode 
dat = StrConv(dat, vbUnicode)

Sample Functions that perform operations that are compatible with byte processing functions of 16-bit versions

In Microsoft Access Visual Basic for Applications, the internal processing of strings is performed using Unicode. Thus, the binary processing functions are different from those of Access Basic used in previous versions of Microsoft Access.

The ANSI function was created to preserve compatibility between the operations of Access Basic and Visual Basic.

Note   Strings input and removed with these ANSI processing functions are always Unicode. After being converted temporarily to ANSI strings within the function, they are restored to Unicode once the process is finished.

The following cannot combine the first and second byte of a DBCS character to create a DBCS character.

AnsiMidB("",1,1) + AnsiMidB("",2,1)

These functions have been created to process strings in byte units. However, a different character cannot be created by the byte-unit processing. In this case, it would be expressed as follows:

StrArg = ""
StrArg = StrConv(StrArg, vbFromUnicode)    ' ANSI 
RetArg = MidB(StrArg,1,1) + MidB(StrArg,2,1)    ' 
    ' 
StrArg = StrConv(StrArg, vbUnicode)    '  Unicode 
RetArg = StrConv(RetArg, vbUnicode)    ' 

Generally, if you convert a string to an ANSI character before processing, you should restore the converted string to a Unicode character after the process is finished.

A byte string process is always a function for processing a string. To process binary data, use a byte Array, not a string variable or a byte string processing function.

A string stored in a byte Array appears as follows:

 Array   
Dim Var() As Byte
Var = ""             ' Unicode 
Var = StrConv("", vbFromUnicode)    ' ANSI 

Function AnsiStrConv(StrArg, flag)
    nsiStrConv = StrConv(StrArg, flag)
End Function

' LenB ANSI   Unicode 
Function AnsiLenB(ByVal StrArg As String) As Long
    AnsiLenB = LenB(AnsiStrConv(StrArg, vbFromUnicode))
End Function

' MidB ANSI   Unicode 
' 
Function AnsiMidB(ByVal StrArg As String, ByVal arg1 As Long, _
            Optional arg2) As String
    If IsMissing(arg2) Then
    AnsiMidB = AnsiStrConv(MidB(AnsiStrConv(StrArg, vbFromUnicode) _
            , arg1),vbUnicode)
    Else
    AnsiMidB = AnsiStrConv(MidB(AnsiStrConv(StrArg, vbFromUnicode) _
            , arg1, arg2), vbUnicode)
    End If
End Function

' LeftB  ANSI   Unicode 
Function AnsiLeftB(ByVal StrArg As String, ByVal arg1 As Long) As String
    AnsiLeftB = AnsiStrConv(LeftB(AnsiStrConv(StrArg, _
            vbFromUnicode), arg1), vbUnicode)
End Function

' RightB ANSI   Unicode 
Function AnsiRightB(ByVal StrArg As String, ByVal arg1 As Long) As String
    AnsiRightB = AnsiStrConv(RightB(AnsiStrConv(StrArg, _
            vbFromUnicode), arg1), vbUnicode)
End Function

' InStrB  2  Ansi Ansi 
Function AnsiInStrB(arg1, arg2, Optional arg3) As Integer
    If IsNumeric(arg1) Then
    pos = LenB(AnsiLeftB(arg2, arg1))
    AnsiInStrB = InStrB(arg1, AnsiStrConv(arg2, vbFromUnicode) _
            , AnsiStrConv(arg3, vbFromUnicode))
    Else
    AnsiInStrB = InStrB(AnsiStrConv(arg1, vbFromUnicode) _
            , AnsiStrConv(arg2, vbFromUnicode))
    End If
End Function

Using byte data type

In Microsoft Access Byte data type is added as a new data type. If a string variable is used when processing binary data, text is converted between ANSI and Unicode, and binary data is changed. Thus, when dealing with binary data, use Byte data type variables.

Dim ByteData() As Byte
ByteData = ""        ' Unicode 
ByteData = StrConv("", vbFromUnicode)     'ANSI 
ByteData = InputB(10, #1)    ' 
Debug.Print ByteData(5)        '