Search broken? Topic is solved

[Paul]

Maybe I'm just dense, but...I cannot figure out the 'Search' function and how it works (or doesn't?).
I enter John Smith in the search box and click the Search button and I get all records in the database. Makes no sense. I should get just one record.
???

search.PNG

[ricklincs]

I had the same problem with a form and after messing around with my form browse sql I got it working correctly. Seem to think it was Group By that caused me the same issue.

[Paul]

How did you fix it?

[kev1n]

Please share your Browse SQL.

[Paul]

My Browse SQL:

Code: Select all

SELECT
  *
FROM
  cases
  INNER JOIN zzzzsys_user 
    ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
    OR ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '13')
    OR ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '15')
   OR ('#USER_ID#' = '68df1c6014e1719' AND pk_inv_case_no = '20')
OR ('#USER_ID#' = '68e0e8f88813bf3' AND pk_inv_case_no = '21')
OR ('#USER_ID#' NOT IN ('68daccef88dbe69','68d858650166554','68d883538d9b830','68d885c9ebe0dee','68df1c6014e1719','68e0e8f88813bf3'))
OR '#GLOBAL_ACCESS#' = '1'
  

[kev1n]

Try enclosing the WHERE clause in additional parentheses

[Paul]

That worked! Thank you!

Corrected code:

Code: Select all

SELECT
  *
FROM
  cases
  INNER JOIN zzzzsys_user 
    ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE (('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
    OR ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '13')
    OR ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '15')
   OR ('#USER_ID#' = '68df1c6014e1719' AND pk_inv_case_no = '20')
OR ('#USER_ID#' = '68e0e8f88813bf3' AND pk_inv_case_no = '21')
OR ('#USER_ID#' NOT IN ('68daccef88dbe69','68d858650166554','68d883538d9b830','68d885c9ebe0dee','68df1c6014e1719','68e0e8f88813bf3'))
OR '#GLOBAL_ACCESS#' = '1');