FIX: Jet 4.0 Treats "#" as Numeric Wildcard Character

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


				

Workaround

Steps to Reproduce Behavior

1. 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