instr syntax SAGE X3

SOLVED

Hi,

I am using the following select criteria to filter my results

instr(1,[F:BOD]CPNITMREF,'X')

which returns things like: HSCX3UAE17,   PXL8,  X0011

However I only want results starting with the letter 'X'.

How do I amend my syntax so that its looks for 'X' in position 1 ONLY, not starts from position 1.

Regards & Thanks

Parents
  • 0

    Hi Lil,

    You may find using Left$([F:BOD]CPNITMREF,1) within the instr() in your criteria works. I am not sure where you are using this criteria to test.

    Regards

    David

  • +1 in reply to David Hoyland
    verified answer

    Hi David,

    Thank you for your reply. I am using this in a requester on the 'Advanced' tab in the 'Selections'\Selection criteria.

    I tried adding your additional syntax above in replace of the '1' but that didn't work, I got three consecutive errors:

     - c:\sage\....\adxodbc : Incompatibility of type

     - Application error. Contact your administrator

     - (Incompatibility of type)

    I tried variating the syntax a few ways to include the Left$ function but each time received the above errors. Can you please advise how the syntax would be correctly included to make this work for me.

    Regards & Thanks

    Lil

  • 0 in reply to Lil
    verified answer

    Hi Lil,

    Sorry I should be been more specific I meant to use this

    instr(1,left$([F:BOD]CPNITMREF,1),'X')

    Certainly in my test I get back all components starting with X 

    The left$ will just include the first character of CPNITMREF, so the instr has just the one character to search to find the X

    Alternatively if it only going to be the first character then this may be better to just use

    left$([F:BOD]CPNITMREF,1)="X"

  • 0 in reply to David Hoyland

    Hi David,

    That works perfectly thank you.

    Kind Regards

    Lil

Reply Children
No Data