Wildcard for Access Query

[pria]

Hi,

I am trying to fetch records from Access database. I am having an error with the LIKE clause applied to the query. My query is something like below

Select A from tablename where B like '??ABCD??OR?'

The question marks is a wildcard, this can hold alpha numberic character.  When I tried executing this query directly in access it works fine. But when I execute this in DIAdem it shoots up an error telling "Exception Occured" when the record is fetched. I tried replacing the ? with * but this does not go through either. Can you help me out how to resolve this issue?

Thanks in advance,

Priya

[Twigeater]

Hello Priya!

Assuming you use ADO to connect to Access you have  to use '_' and '%' as wildcards for the common '?' / '*'. This is nothing DIAdem specific, it is common for SQL!

I can't reproduce the error you got. How do you open the connection?

Matthias

[pria]

I am using the below code

 MdbName = "C:\Example.mdb"

 Set oAdoConnection = CreateObject("ADODB.Connection")
 oAdoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=" & MdbName & ";Persist Security Info=False"
 oAdoConnection.Mode = adModeReadWrite
 oAdoConnection.Open
 If oAdoConnection.Errors.Count Then
    msgbox err.description
 else
    Call MsgBoxDisp ("Connected to the Database     Successsfully","MB_NOBUTTON" ,"MsgTypeNote",,3,True)
 End if

  oAdoConnection.CursorLocation = adUseClient
  Set RecordSet = CreateObject("ADODB.RecordSet")
  RecordSet.ActiveConnection = oAdoConnection
  sqlCmd = "Select A from tablename where B like '??ABCD??OR?'"
  RecordSet.Source = sqlCmd
  RecordSet.CursorType = adOpenKeyset
  RecordSet.LockType = adLockOptimistic
  RecordSet.Open
  msgbox RecordSet.Fields("A")
  RecordSet.Close

I get the error at msgbox RecordSet.Fields("A"), the same query goes fine and fetches me the records in acess

[pria]

Thanks, I was able to run it using %, the reason why I dint try it is because when I directly used % in access no records were returned, but this works through DIAdem.

[Brad_Turpin]

Hi Priya,

Note that as Twigeater initial posted, the "_" SQL wildcard is identical in function to the Windows "?" wildcard, and the "%" SQL wildcard is identical in function to the Windows "*" wildcard.  The "%" SQL wildcard is not a one-for-one replacement for the "?" you were attempting to use.  The exact corresponding query would be:

Select A from tablename where B like '__ABCD__OR_'

Brad Turpin
DIAdem Product Support Engineer
National Instruments