Problem involving LIKE and wildcard in SQL string defining ADO rec

[Guest]

Hi,

I am having problems with the WHERE clause:

Sub SetupWordTemplate(frmFilter As Form_frmX)

Dim rst As New ADODB.Recordset
Dim strSQL As String


strSQL = "SELECT ID, Type, Description, Purpose, " & _
"Contract, Required, [Date Reqd] FROM qryGfpFormatted " & _
"WHERE Contract LIKE " & "'some_string'"

rst.Open strSQL, CurrentProject.Connection

If some_string is replaced by the characters that match characters in the
field Contract, then all is ok. But if some_string is replaced by * then a
syntax error results:

i.e. "WHERE Contract LIKE " & "'*'" then syntax error results

What I really want to do is replace some_string by a reference to a combo
box that can include exact match to Contract or contain an * or a few
characters followed by an *.

i.e. "WHERE Contract LIKE " & "'frmFilter.cboContract'"

Appreciate any help.

John

 

[Rick Brandt]

Hi,

I am having problems with the WHERE clause:

Sub SetupWordTemplate(frmFilter As Form_frmX)

Dim rst As New ADODB.Recordset
Dim strSQL As String


strSQL = "SELECT ID, Type, Description, Purpose, " & _
"Contract, Required, [Date Reqd] FROM qryGfpFormatted " & _
"WHERE Contract LIKE " & "'some_string'"

rst.Open strSQL, CurrentProject.Connection

If some_string is replaced by the characters that match characters in
the field Contract, then all is ok. But if some_string is replaced
by * then a syntax error results:

i.e. "WHERE Contract LIKE " & "'*'" then syntax error results

What I really want to do is replace some_string by a reference to a
combo box that can include exact match to Contract or contain an * or
a few characters followed by an *.

i.e. "WHERE Contract LIKE " & "'frmFilter.cboContract'"

Appreciate any help.

John

In ADO the wildcard character is % rather than *.

 

[Guest]

Great, thanks.

Will % behave exactly as *?

I have several parameter screens (that provide parameters to queries) that
do use the *. To keep the interface consistent, I suppose I can let the user
use the * and in the after update event of each control, do some string
manipulation to replace any * by a % to a variable, then pass these variables
as arguments to the subprogram containing the ADO statement, then use this
variable instead of reference to the control.

Is there anything off hand that would be more efficient?

Thanks again,

John

 

[Rick Brandt]

Great, thanks.

Will % behave exactly as *?

I have several parameter screens (that provide parameters to queries)
that do use the *. To keep the interface consistent, I suppose I can
let the user use the * and in the after update event of each control,
do some string manipulation to replace any * by a % to a variable,
then pass these variables as arguments to the subprogram containing
the ADO statement, then use this variable instead of reference to the
control.

Is there anything off hand that would be more efficient?

I have never used ADO, but in web pages where I have to do the same thing I
usually just let the user type * and then my code replaces it in background
query. I wouldn't change what they see on the screen as that would likely just
confuse them.

 

[Guest]

I have never used ADO, but in web pages where I have to do the same thing I
usually just let the user type * and then my code replaces it in background
query. I wouldn't change what they see on the screen as that would likely just
confuse them.

Exactly, that's why I would make the substitution in a variable and leave
the control alone.

Thanks again.