FIX: Jet 4.0 Treats "#" as Numeric Wildcard Character (259345)

SYMPTOMS

When you use Microsoft Jet 4.0 and execute a SQL statement similar to the following, Jet 4.0 interprets the hash-mark character (#) as a numeric wildcard instead of a character literal, which causes incorrect results to be returned:

Select * from Table1 where Comments like '#%'

This behavior does not occur with Jet 3.51, which permits records containing the hash-mark character to be returned. In addition, the SQL statement returns the same results when run against both Jet and SQL Server. The Jet 4.0 behavior no longer provides this interoperability.

RESOLUTION

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:

   Date      Time    Version      Size             File name     Platform
   ----------------------------------------------------------------------
   3/21/00           4.0.3921.0   241,936 bytes    msjtes40.dll

Workaround

Enclosing the hash-mark character in square brackets ([ ]) forces Jet to evaluate it as a character literal. For example:

Select * from Table1 where Comments like '[#]%'

MORE INFORMATION

Steps to Reproduce Behavior

Copy the following code into a Visual Basic project, add a reference to the Microsoft ActiveX Data Objects library, and compile it. Please note that you may need to change the path to your database file. Note that the message box displays only those records that begin with a numeric character.

On Error GoTo Wildcard_err
Dim conn As Object
Dim rs As Object
Dim strResults As String

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb"

conn.Execute "Create table HashmarkTest (Val varchar(10))", , adExecuteNoRecords
conn.Execute "Insert into HashmarkTest values ('1abc')", , adExecuteNoRecords
conn.Execute "Insert into HashmarkTest values ('#abc')", , adExecuteNoRecords
conn.Execute "Insert into HashmarkTest values ('2def')", , adExecuteNoRecords
conn.Execute "Insert into HashmarkTest values ('#def')", , adExecuteNoRecords

rs.Open "Select * from HashmarkTest where Val like '#%'", conn
'Comment the line above and uncomment the line below to retrieve correct results.
'rs.Open "Select * from HashmarkTest where Val like '[#]%'", conn

Do While Not rs.EOF
strResults = strResults & rs!Val & vbCr
rs.MoveNext
Loop

MsgBox strResults

rs.Close
conn.Execute "Drop table HashmarkTest", , adExecuteNoRecords
conn.Close
Exit Sub

Wildcard_err:
MsgBox conn.Errors.Item(0).Description
Resume Next