Hi,

Can anyone help me with this statement I am trying to query a Access database using the following code but I get an SQL error Syntax error (missing operator) in query expression. Is it possible to use strpos in a SQL statement?

pro_id is a field in the database whose value is 489

$valves = 489;

$SQLQuery = "SELECT * FROM Products WHERE strpos(".$valves.",pro_id,1) !== 0";

    Thanks for your reply, I tried using the POSITION statment but still get an error this is what I replaced it with....

    $SQLQuery = "SELECT * FROM Products WHERE POSITION('ab' IN 'abcde') !== 0";

    error says....

    Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'POSITION('ab' IN 'abcde') !== 0'., SQL state 37000 in SQLExecDirect

      Are you sure the !== operator is a legal mysql operator?

      I would think changing it to

      SELECT * FROM Products WHERE POSITION('ab' IN 'abcde') <> 0

      <> is the SQL not equals operator. Don't go expecting SQL to do anything in any way shape or form the same as C or PHP. It hardly ever does.

        And don't expect MSAccess functions and syntax to resemble standard SQL in any shape or form either, cos it rarely does.

        The Access equivalent of POSITION is InStr([start, ]string1, string2[, compare])

        It returns position of first occurence using base 1 not 0: 0 is used to indicate no match found.

        $SQLQuery = "SELECT * FROM Products WHERE InStr('ab', 'abcde', 1) >0";

        This would return only those where 'ab' was found. The compare switch is for binary or text comparison, in this case I've set it to text - not case-sensitive.

          Wow. There are times I'm really glad Microsoft does NOT make a version of Access for Linux environments...

            Thanks for your help, yes the instr worked.

            Cheers

              InStr is also the Oracle function - and probably came first knowing Oracle.

                Sxooter wrote:

                Wow. There are times I'm really glad Microsoft does NOT make a version of Access for Linux environments...

                My heart agrees - but my head sez "There would be that much more work for you if there were." being as I've been programming the beast for 10 years.

                And it still amazes me with it's confounding behaviour at least once a month. :glare:

                  Write a Reply...