2

I am running below query in access in which i need to take argument from end user. Following query is used. However, when i don't ask for user input & put the condition in the query like this (tblApplications.txtApplicant Like "*dac*") it returns values and as soon as i asked for user input it returns blank.

Below is the query:

SELECT
    tblConstruction.txtConstructionNr AS [Constr-Nr] AS Amount,
    tblApplications.txtFolder
FROM
    tblApplications
    LEFT JOIN tblConstruction
        ON tblApplications.IDConstAppli = tblConstruction.IDConstr
WHERE
    (((tblApplications.txtApplicant) Like "*" & [Please enter the applicant or parts thereof:] & "*") AND
    ((tblApplications.txtDecission) Like "open"))
ORDER BY
    tblApplications.txtApplicant,
    tblApplications.[txtKomm-Nr],
    tblApplications.txtDecissionDat DESC;
HansUp
  • 95,961
  • 11
  • 77
  • 135
Maqsood
  • 369
  • 4
  • 17
  • how do you run your query? – 4dmonster Sep 28 '18 at 13:59
  • with "run" option @4dmonster – Maqsood Sep 28 '18 at 14:05
  • 1
    Have you selected "SQL Server Compatible Syntax" in Access options? If so use `%` instead of `*` as the `Like` wildcard. – HansUp Sep 28 '18 at 14:13
  • 1
    But `tblAntraege` is not included in your query, so how should it possibly work? – Gustav Sep 28 '18 at 14:16
  • @Gustav query was edited by another user & he forget to update in the body. – Maqsood Sep 28 '18 at 14:31
  • @HansUp i am new to Access. I dont know where to set this option. Also i would like to tell you Tables are linked to Mysql. Just to update i change to "%" and again it works fine when i give condition in query but as soon as i changed to user input. It stops working – Maqsood Sep 28 '18 at 14:39
  • Looking closer, I suspect it may not matter. You question shows `tblApplications.txtApplicant Like "dac"` works. But when I look at the raw text, it seems you may have meant to tell us that `tblApplications.txtApplicant Like "*dac*"` works. If that is the case, it's not the wildcard problem I suspected. And you should fix the question so it displays correctly. – HansUp Sep 28 '18 at 14:43
  • @HansUp yes it works `tblApplications.txtApplicant Like "*dac*"` but its not working for the input – Maqsood Sep 28 '18 at 14:45

1 Answers1

0

If your table is linked to MySQL, use the MySQL wildcards:

%   matches any number of characters, even zero characters.

_    matches exactly one character.

Instead of * and ? as for Access.

...
tblApplications.txtApplicant Like "%" & [Please enter the applicant or parts thereof:] & "%"
...

The collation of the column might be case sensitive. Try entering the search term in the right case.

Community
  • 1
  • 1
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • As mentioned earlier changing to `%` is not working. And I gave the term in right case even then its not returning any row – Maqsood Sep 28 '18 at 15:08