Find function in Access Query

Question

Hello!  I have an excel formula  to trim the material by deleting all  the -P*...  =left(A1,find(A1,"-P")-1)..    How do i convert this into formula for access query.  I tried  using   Trim(Left([Material],InStr([Material],"-P")-1)) but it only trim the materials with only -P and not -P*.  How  do I trim down all the -P*?

Example :

  Material            Ideal Results

  23-3456-09-P       23-3456-09

  45-789343-PTA     45-789343

  A345FG-02-PA      A345FG-02

Answer 1

Try this:

SELECT Table1.Material

  , [Material] Like '*-P*' AS IsCandidate

  , InStr([Material],'-P') AS TrimStart

  , Left$([Material],IIf(InStr([Material],'-P')>0,InStr([Material],'-P')-1,Len([Material]))) AS Result

FROM Table1;