Using Likes and Wildcards in VB DAO 1

[Anubis3000]

Hi,

I am trying to execute a query from vb that takes parameters from a form. Currently this is my code:

Dim db As dao.Database
Dim rst As dao.Recordset
Set db = CurrentDb
Dim strsql As String
Dim qdf As dao.QueryDef

Set qdf = db.CreateQueryDef("", "SELECT [REPORT].[REPORT_NUMBER], [REPORT].[REPORT_TITLE] FROM REPORT WHERE ([REPORT].[REPORT_TITLE] aLike ""*(REPORT.REPORT_TITLE) aLike " * " & tester & " * " Or (REPORT.REPORT_TITLE) Like [forms]![RPT_QRY]![REPORT_TITLE] Is Null))

qdf(0) = [Forms]![RPT_QRY]![REPORT_TITLE]

Set rst = qdf.OpenRecordset(dbOpenSnapshot)

When I try to run it, either the sql statement is incorrect, or access does not recognize my parameter (qdf(0)). Any suggestions? Thnx.

 

[PHV]

What is tester in your code ?
You wanted this ?
strSQL = "SELECT REPORT_NUMBER, REPORT_TITLE FROM REPORT"
If Trim(Forms!RPT_QRY!REPORT_TITLE & "") <> "" Then
strSQL = strSQL & " WHERE REPORT_TITLE Like '*" & Forms!RPT_QRY!REPORT_TITLE & "*'"
End If
Set qdf = db.CreateQueryDef("", strSQL)
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Anubis3000]

Works great. Just what I needed. Thanks!

 

[Anubis3000]

I've actually run into a small problem. When the text or in the box "Forms!RPT_QRY!REPORT_TITLE" contains an apostrophe, the query genrates a syntax error. I'm not sure how to fix it....

 

[PHV]

strSQL = strSQL & " WHERE REPORT_TITLE Like '*" & Replace(Forms!RPT_QRY!REPORT_TITLE, [tt]"'", "''") & "*'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886