Why doesn't Like work

[nkamp]

Hallo,

The following what I do not understand:

  Set cmd = New ADODB.Command
  Set cmd.ActiveConnection = CurrentProject.Connection
  cmd.CommandType = adCmdText

  cmd.CommandText = "SELECT par1 FROM tblparameters Where tblparameters.gcnf = 'XMLexp' AND ((tblparameters.ccnf) Like 'ExpTijd*')"
  Set rec = cmd.Execute()
  
  Do While rec.EOF = False
    MsgBox rec("par1").Value
    rec.MoveNext
  Loop

I don't get any result back. If I changed it likt the following:
tblparameters.ccnf = 'ExpTijd1' , in the query, I get one record back.
So my conclusion the query is right but the Like doesn't work in these circumstances?

thanks in advance.

Nico

 

[MStef]

Try this;
..........Like 'ExpTijd' & "*")"

 

[nkamp]

I have found it. It must 'ExpTijd%' in stead of 'ExpTijd*'. So now it's working.

The only thing what I not understand is that if you are looking in the books or search with google on the internet you see everywhere examples with the "*". Why? Can somebody give me a explanation for this?

Another thing what I not understand why can't be used a table with the name parameter? Is this a keyword of Access? The stranges thing if you build a form with objectdependencie on the table parameter it's working but if you use SELECT * FROM parameter, you get a error about the FROM clause.

Thanks anyway.

Nico

 

[Pat Hartman]

Jet/ACE (Access database engine) uses the * as the wild card character and SQL Server uses the %. Your tables must be SQL Server or some other RDBMS that uses the %. When you create querydefs, you use Jet/ACE syntax so you would use the *. The ODBC driver then converts your Access SQL to whatever the target RDBMS required. When you write the code yourself in VBA, you are going direct and not through the ODBC driver so you can't use Access syntax. You MUST use the syntax of the target RDBMS.