none
expression in query RRS feed

  • Question

  • hello ,

    I am trying to create an expression in a query but I have no idea how to do it 

    what I have is numbers like this 

    1/p222-001

    11/p333-002

    p444-001/10

    pb333-050/10

    11/pb444-005

    what I need id the results to only show me the letters/numbers without the numbers before or after the "/"

    like  p222-001,    p333-002,   p444-001, pb333-050, pb444-005

    thanks for your help

    steve

    Tuesday, January 23, 2018 1:23 AM

Answers

  • Hello Stevek840,

    You could embed an IIF statement outside previous expression. If field contains "/", execute previous expression. If not, return this field directly.

    Here is the example.

    IIF(InStr([TestID],"/")>0,
        IIf(
             IsNumeric(Left([TestID],InStr([TestID],"/")-1)),
             Mid([TestID],InStr([TestID],"/")+1),
             Left([TestID],InStr([TestID],"/")-1)
             ),
        [TestID]
    )

    Besides, we support one issue for one thread. If you original issue has been resolved, I would suggest mark answer to close current thread. If you have any further issue, please feel free to post new thread to let us know.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by stevek840 Friday, January 26, 2018 7:40 AM
    Thursday, January 25, 2018 5:50 AM

All replies

  • > what I have is numbers like this: 1/p222-001

    That is not a number. (in the immediate window)
    ? IsNumeric("1/p222-001")
    False

    It appears if you want to pick apart this string into its constituents, and only return the relevant parts.
    You can do that with string manipulation functions such as InStr, Left, Mid, and even Split. Check with the Help file on how to use these.


    -Tom. Microsoft Access MVP

    Tuesday, January 23, 2018 5:38 AM
  • Hello steve,

    Please try below expression.

    IIf(
    IsNumeric(Left([TestID],InStr([TestID],"/")-1)),
    Mid([TestID],InStr([TestID],"/")+1),
    Left([TestID],InStr([TestID],"/")-1)
    )

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 24, 2018 5:12 AM
  • Hi Terry 

    thanks for your help.

    this is what  I have done to the formular you gave me 

    Expr2: If(IsNumeric(Left([parts]![part number],InStr([parts]![part number],"/")-1)),Mid([parts]![part number],InStr([parts]![part number],"/")+1),Left([parts]![part number],InStr([parts]![part number],"/")-1))

    I am getting the error message 

    undefined function "if" in expression 

    any help appreciated 

    steve

    Wednesday, January 24, 2018 10:47 AM
  • It's Iff( not If(

    Wednesday, January 24, 2018 2:05 PM
  • It's Iff( not If(

    Hi Lawrence,

    Probably you mean IIf.

    Imb.

    Wednesday, January 24, 2018 2:16 PM
  • Sorry yes IIf(

    Old Age and poor eyesight. Ha!

    Wednesday, January 24, 2018 4:18 PM
  • Hill steve840,

    As Lawrence and Imb said, it is "IIF" rather than "IF". Please check if changing "IF" to "IIF" could work you. If it does, I would suggest you mark helpful reply to close the thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by stevek840 Thursday, January 25, 2018 5:11 AM
    • Unmarked as answer by stevek840 Thursday, January 25, 2018 5:37 AM
    Thursday, January 25, 2018 1:16 AM
  • hi ,

    I have just run it through all my files and I have missed a piece 

    if the part does not have any "/" in it. is shows the error

    for example  if I have p222-001   or pb222-001  I need it to show the same 

     sorry for this part missed in my first post 

    thanks in advance 

    steve

    Thursday, January 25, 2018 5:40 AM
  • Hello Stevek840,

    You could embed an IIF statement outside previous expression. If field contains "/", execute previous expression. If not, return this field directly.

    Here is the example.

    IIF(InStr([TestID],"/")>0,
        IIf(
             IsNumeric(Left([TestID],InStr([TestID],"/")-1)),
             Mid([TestID],InStr([TestID],"/")+1),
             Left([TestID],InStr([TestID],"/")-1)
             ),
        [TestID]
    )

    Besides, we support one issue for one thread. If you original issue has been resolved, I would suggest mark answer to close current thread. If you have any further issue, please feel free to post new thread to let us know.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by stevek840 Friday, January 26, 2018 7:40 AM
    Thursday, January 25, 2018 5:50 AM