[RESOLVED] Parameters in Query Using "LIKE" statement

I'm sure I have a few issues in this statement, but I believe my main issue comes from where my "LIKE" statement resides.

I'm using parameters in VB 6 -- hence my post to this forum and not the database forum. Apologies if that assumption is wrong.

Anyway, here's what I have and it's not currently working.

Code:

---

  strSQL = "SELECT PROD.CSU_Clinic_Info.Clinic_Date, PROD.CSU_Clinic_Info.SID_DOC_NUM, " & _
              "PROD.CSU_Clinic_Info.Day_Of_Week, PROD.CSU_Clinic_Info.ID " & _
              "FROM PROD.CSU_Clinic_Info " & _
              "WHERE (PROD.CSU_Clinic_Info.Clinic_Date >= ? " & _
              "AND PROD.CSU_Clinic_Info.Clinic_Date <= ?) " & _
              "AND PROD.CSU_Clinic_Info.Id Like %? " & _
              "AND LEN(PROD.CSU_Clinic_Info.ID) = Len(?) " & _
              "AND PROD.CSU_Clinic_Info.Day_Of_Week = ? " & _
              "AND PROD.CSU_Clinic_Info.Cancelled <> 'Y' " & _
              "AND PROD.CSU_Clinic_Info.Institution = ? " & _
              "ORDER BY CLINIC_DATE ASC"

---

It returns Incorrect syntax near '@P3'

Can you tell us what the parameter value is?

It's a string. Value is "97"

Initial thought: Instead of... Like %?
Maybe... Like '%' + ?
Or including the % in the parameter value?

I think I got it. One of my parameters was incorrect. Seems to be okay now.

Quote:

---

Originally Posted by The_Grudge

I think I got it. One of my parameters was incorrect. Seems to be okay now.

---

Seems I was mistaken. Query runs but doesn't return matches -- and it should. If I hard code a value in it works so something about that "LIKE" it doesn't -- ahem -- like.

Here's what I had to do to get it working....that Like statement looks like this

AND PROD.CSU_Clinic_Info.Id Like '%' + ?

So, your recordset has both EOF & BOF set to true?

Maybe you can re-post your query and the parameter values, for each parameter. We might find something that looks iffy?

I'd also suggest running a simpler query with just the LIKE statement/criteria. If it returns records then one or more of your other criteria is not being met.

Edited: I probably should've refreshed before I posted. Sounds like you found the solution. Your solution, by the way, was what I suggested in post #4 above.

Quote:

---

Originally Posted by The_Grudge

AND PROD.CSU_Clinic_Info.Id Like '%' + ?

---

Question: is that column of type text or numeric?
i was always under the impression that LIKE only works on text-patterns.

if text, i would expect something like

AND PROD.CSU_Clinic_Info.Id Like '% + ? +%'

Quote:

---

Originally Posted by Zvoni

Question: is that column of type text or numeric?
i was always under the impression that LIKE only works on text-patterns.

---

I actually had that question myself and tested the LIKE on a numeric field (even fields that contained NULL values). No problem in SQL Server which is what I'm assuming that the_grudge is using.

I also wondered about not having the wild card suffixed. Just assumed he was looking for values that ended with 97.