"Like" problem in SELECT Statement

[rich.barry]

I am having an issue with a select statement containing a LIKE clause where the record set returned is empty, but if I Debug.Print the select statement and copy it to the SQL view of a query and run it there, then it returns a ful set of records.

Record.Open "SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '" & [Forms]![frmDateTime]![cboDefault] & "*" & "')", db, adOpenKeyset, adLockOptimistic

In a query both of these return records
SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '*')
SELECT * FROM qryVeneerWidths

In code, as a record set, only the second one does.

Can any one tell me what the problem with the LIKE statement in the recordset Select clause is.


Thanks

Richard

 

[KernelK]

What value is in your combo box?

What is the value coming from [Forms]![frmDateTime]![cboDefault]? Because if you look at the concatenation string you are forming vs. the one that you state works in a query you will see that:

SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '*')

is very different from

"SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like 'somevalue*')"

which is what your string looks like when you put it together. Quick way to see what the string looks like with a value would be to put a msgbox statement in your code right before the open statement you've given as an example:

msgbox "SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '" & [Forms]![frmDateTime]![cboDefault] & "*" & "')"

That way you can see what your fully formed SQL string looks like to debug it. Hope that helps some.

 

[Pat Hartman]

If you are using a combo, why are you using LIKE? LIKE is ONLY used when the user enters partial text strings. It is never used when the user enters a numeric value or chooses an item from a combo.

 

[rich.barry]

Hello Pat
The combobox has a list of my GradingRule s....NPC, NPD, Sei etc
Normally I'm happy selecting one of the combobox entries, but now also want the ability to be able to select NP* to analyse all the data relating to the NP rules.

I can see the fully formed Select statement when I do a debug.print, rather than using a msgbox.
I've also tested using Chr(34) rather than an apostrophe, but this makes no difference.

Rich