0

This is in continutation of the question asked here: how to use LIKE with column name

since i do not have enough reputation i am unable to add it to the question there itself. My question is:

  1. I am using Microsoft Access with ODBC connectivity to MySql

  2. There is a linked table to it

  3. i wan to query the table using a text box on a Form

  4. i am unable to do so with the error 'ODBC--call failed'

this is the sql statement of my query:

SELECT 
    'Completed',  
    'Entity Name',  
    'Assignment',  
    'Financial Year',  
    'Service Area',  
    'Partner/Director',  
    'Intern 1',  
    'Intern 2',  
    Payment_recddate,
    Invoice_Amount',  
    'MinOfStart Date',  
    'MinOfDue Date',  
    PAN,
    AssignmentID,  
    Priority1CompletedRatio
FROM 
    qry_Assignments_WIP_sub
WHERE 
    [Entity Name] Like 
        CONCAT([Forms]![frm_Assignments_WIP]![txtEntitynameFilter],'%')

I am doing something wrong in it and am at wits end trying to get this right enter image description here

In reply to cha WHERE qry_Assignments_WIP_sub.Entity Name Like "CONCAT('" & [Forms]! [frm_Assignments_WIP]![txtEntitynameFilter] & "','%')"')

still getting the errorenter image description here

Eager Einstein
  • 40
  • 10
  • Reboot your computer and then check your connection to the database. I think this is more of a question for Server Fault or Superuser... it's not really programming related. – ashleedawg Apr 05 '18 at 04:54
  • trying since last 3 days, error still persists! It has something to do with syntax of the WHERE and LIKE. If i remove the WHERE line, i do get a result. There is blank result when i ask the query to take the value from the Form field. – Eager Einstein Apr 05 '18 at 04:57
  • omg I can read the code now. Isn't it better when it's cleaned up? It's still the same code. Anyhow I'm not sure if those `'` were there in your code or you added them on the site, but they shouldn't be there. Also what are you trying to accomplish with `CONCAT([Forms]![frm_Assignments_WIP]![txtEntitynameFilter],'%')`?\ – ashleedawg Apr 05 '18 at 05:01
  • Also are you using**[tag:mysql] or [tag:ms-access]**? There are a lot of differences, including deciding whether you're using the correct wildcard. **Please delete any irrelevant tags.** – ashleedawg Apr 05 '18 at 05:02
  • as i mentioned i am using access on mysql database and the query is selected to be a passthrough. don't get it why u mentioning 'Please delete any irrelevant tags' – Eager Einstein Apr 05 '18 at 05:25
  • You can't use `Forms!...` in a Pass-Through query. See https://stackoverflow.com/questions/48120992/ms-access-using-forms-in-a-pass-through-query – Andre Apr 05 '18 at 06:11
  • @andre still now working! :( – Eager Einstein Apr 05 '18 at 13:30
  • @Andre , i am getting an error when the cursor comes to the part Set rst = .OpenRecordset – Eager Einstein Apr 05 '18 at 13:50
  • *i am getting an error* - you'll have to be more specific than that. Add the code you are using to your question, and state the full error. – Andre Apr 05 '18 at 17:31

1 Answers1

0

I think the problem is with the last line. Looks like you are trying to use a value from the form and not inserting the actual value. What you need to do is this:

"CONCAT('" & [Forms]![frm_Assignments_WIP]![txtEntitynameFilter] & "','%')"
cha
  • 10,301
  • 1
  • 18
  • 26