Hello,
Using Design View, I have a query with LIKE ‘%’ || :Search || ‘%’ as a user parameter (used to do a keyword search). This worked well when I used LO Base with HSQLDB, but since I am using LO (6.2.5) as a front end and MySQL server this user parameter is returning all my records.
How do I set up this query to allow a user parameter search for text (partial or full)?
Thanks,
Jody
Hello,
Try:
Like concat('%', :Search, '%')User parameter window isn’t appearing
-jody
Sorry but don’t understand your comment. You seem to have accepted the answer but contradict this with the comment. Have tested on Mint 18.3 without problem using LO v6.3.0.4. Is this working for you? If not, exactly what is happening?
Sorry if I hit the tick by mistake.
The object is to have a user enter a string of text and have the query return matches. Example: A person could enter ardi into a user parameter window and it would return the records containing Bombardier.
I’m on W10 with MySQL Server 8.0 btw
Yes, that is what is done in my answer. Prompt is displayed, enter condition and result is displayed where entered data is somewhere within specified field. What exactly is your SQL and what specific problem are you having?
Have just re-tested but I am using Linux Mint with local MySQL Serrver and LO v6.3.0.4.
SELECT Company FROM reservations.corporate info corporate info WHERE Company = ‘Like concat(’’%’’, :Search, ‘’%’’)'
LO 6.2.5.2, W10 with MySQL Server 8.0. The user parameter window isn’t appearing
Don’t know why you chose to surround the LIKE section with apostrophes. That actually signifies what it is equated to. Also you have = and LIKE together. Also problem in concat section. Statement should be:
SELECT `Company` FROM `reservations`.`corporate info` `corporate info` WHERE `Company` LIKE concat('%', :Search, '%')I copy/pasted your original response into design view and it put in the apostrophes. When I pasted into SQL view it works.
Thanks for your patience.