Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Text Sparing within a Query

Discussion in 'Information Technology' started by Dannyboy1001, Jul 28, 2006.

  1. Dannyboy1001

    Dannyboy1001
    Expand Collapse
    Guest

    I have built a query from a table. Inside the query I need to create a new
    field, that takes an existing field from the table and "spares" out a portion
    of the data. All the data within the field (table) follows the same format
    (aaa_bbb_ccc). I need to extract out the middle piece.

    I tired setting up an expression using the split function

    Expr1:IIf([thread_name]="","NULL";split([thread_name],"_",2))

    This does not work.

    Any help would be appreciated. I need to get this done for a presentation
    due next week (6-12-06). Thanks again.
     
  2. Loading...

    Similar Threads Forum Date
    Malaysia Hindus, Sikhs In Malaysia Appalled With University Textbook Ridiculing Their Faiths Breaking News Jun 14, 2016
    Hard Talk Taking One Liners Out Of Context From Gurbani To Allow Intellectual Independence? Hard Talk Apr 23, 2016
    India Protests in India Over the Desecration of a Holy Text Leave Two Dead Breaking News Oct 16, 2015
    Learn Punjabi Convert Punjabi text Language, Arts & Culture Oct 11, 2013
    Sikh Coalition Harmeet Kaur Took the Lead ! First to Respond to Textbook Challenge ! Sikh Organisations May 25, 2013

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Dannyboy,

    Try it like this...
    Mid([threadname],5,3)

    --
    Steve Schapel, Microsoft Access MVP

    Dannyboy1001 wrote:
    > I have built a query from a table. Inside the query I need to create a new
    > field, that takes an existing field from the table and "spares" out a portion
    > of the data. All the data within the field (table) follows the same format
    > (aaa_bbb_ccc). I need to extract out the middle piece.
    >
    > I tired setting up an expression using the split function
    >
    > Expr1:IIf([thread_name]="","NULL";split([thread_name],"_",2))
    >
    > This does not work.
    >
    > Any help would be appreciated. I need to get this done for a presentation
    > due next week (6-12-06). Thanks again.
     
  4. Dannyboy1001

    Dannyboy1001
    Expand Collapse
    Guest

    One more thing I forgot to let you know. The format of the middle part can
    be between 2-5 characters (i.e. aaa_bb_ccc or aaa_bbbb_ccc or aaa_bbb_ccc)

    "Steve Schapel" wrote:

    > Dannyboy,
    >
    > Try it like this...
    > Mid([threadname],5,3)
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Dannyboy1001 wrote:
    > > I have built a query from a table. Inside the query I need to create a new
    > > field, that takes an existing field from the table and "spares" out a portion
    > > of the data. All the data within the field (table) follows the same format
    > > (aaa_bbb_ccc). I need to extract out the middle piece.
    > >
    > > I tired setting up an expression using the split function
    > >
    > > Expr1:IIf([thread_name]="","NULL";split([thread_name],"_",2))
    > >
    > > This does not work.
    > >
    > > Any help would be appreciated. I need to get this done for a presentation
    > > due next week (6-12-06). Thanks again.

    >
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Dannyboy,

    Ok, just to save another round... how many characters can the first
    section be?

    --
    Steve Schapel, Microsoft Access MVP

    Dannyboy1001 wrote:
    > One more thing I forgot to let you know. The format of the middle part can
    > be between 2-5 characters (i.e. aaa_bb_ccc or aaa_bbbb_ccc or aaa_bbb_ccc)
     
  6. Dannyboy1001

    Dannyboy1001
    Expand Collapse
    Guest

    In the first part, 2-6 characters, the second part 2-5 characters, the third
    part 2-3 characters and there is an optional 4th part which can be any
    length, but isn't used very often. Now you know everything I know...which in
    my case isn't much...lol.

    Thank you for your assistance.

    "Steve Schapel" wrote:

    > Dannyboy,
    >
    > Ok, just to save another round... how many characters can the first
    > section be?
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Dannyboy1001 wrote:
    > > One more thing I forgot to let you know. The format of the middle part can
    > > be between 2-5 characters (i.e. aaa_bb_ccc or aaa_bbbb_ccc or aaa_bbb_ccc)

    >
     
  7. Jen

    Jen
    Expand Collapse
    Guest

    Since it appears that there is an underscore separating the parts, try using
    the InStr function.

    something like:
    mid$([field],instr(1,[field],"_"),instr(7,[field],"_")-instr(1,[field],"_")+1)

    alternatively, you can pick off the right part after the underscore, then
    pick off the left part.

    Enjoy. j.

    "Dannyboy1001" wrote:

    > I have built a query from a table. Inside the query I need to create a new
    > field, that takes an existing field from the table and "spares" out a portion
    > of the data. All the data within the field (table) follows the same format
    > (aaa_bbb_ccc). I need to extract out the middle piece.
    >
    > I tired setting up an expression using the split function
    >
    > Expr1:IIf([thread_name]="","NULL";split([thread_name],"_",2))
    >
    > This does not work.
    >
    > Any help would be appreciated. I need to get this done for a presentation
    > due next week (6-12-06). Thanks again.
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Dannyboy,

    Well, I'm not sure about knowing as much as you, but hopefully I now
    know enough to be able to answer the question :).

    Ok, let's try it like this...
    Mid([threadname],InStr([threadname],"_")+1,InStr(InStr([threadname],"_")+1,[threadname],"_")-InStr([threadname],"_")-1)

    If you need to do it often, it may be worth writing a little
    user-defined function.

    --
    Steve Schapel, Microsoft Access MVP

    Dannyboy1001 wrote:
    > In the first part, 2-6 characters, the second part 2-5 characters, the third
    > part 2-3 characters and there is an optional 4th part which can be any
    > length, but isn't used very often. Now you know everything I know...which in
    > my case isn't much...lol.
    >
     
  9. Dannyboy1001

    Dannyboy1001
    Expand Collapse
    Guest

    "Steve Schapel" wrote:

    > Dannyboy,
    >
    > Well, I'm not sure about knowing as much as you, but hopefully I now
    > know enough to be able to answer the question :).
    >
    > Ok, let's try it like this...
    > Mid([threadname],InStr([threadname],"_")+1,InStr(InStr([threadname],"_")+1,[threadname],"_")-InStr([threadname],"_")-1)
    >
    > If you need to do it often, it may be worth writing a little
    > user-defined function.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Dannyboy1001 wrote:
    > > In the first part, 2-6 characters, the second part 2-5 characters, the third
    > > part 2-3 characters and there is an optional 4th part which can be any
    > > length, but isn't used very often. Now you know everything I know...which in
    > > my case isn't much...lol.
    > >

    > Steve, thanks a million....it works. The only thing I need to do is put an IIF stattement in the expression so any blank fields don't diaplay as an #ERROR, but think I can handle that.....again Thanks you're the best :) I can now start analyizing our alerts by vendor and see who is having issues.
     
  10. Dannyboy1001

    Dannyboy1001
    Expand Collapse
    Guest

    Steve, thanks a million....it works. The only thing I need to do is put an
    IIF stattement in the expression so any blank fields don't diaplay as an
    #ERROR, but think I can handle that.....again Thanks you're the best :) I can
    now start analyizing our alerts by vendor and see who is having issues.

    "Dannyboy1001" wrote:

    >
    >
    > "Steve Schapel" wrote:
    >
    > > Dannyboy,
    > >
    > > Well, I'm not sure about knowing as much as you, but hopefully I now
    > > know enough to be able to answer the question :).
    > >
    > > Ok, let's try it like this...
    > > Mid([threadname],InStr([threadname],"_")+1,InStr(InStr([threadname],"_")+1,[threadname],"_")-InStr([threadname],"_")-1)
    > >
    > > If you need to do it often, it may be worth writing a little
    > > user-defined function.
    > >
    > > --
    > > Steve Schapel, Microsoft Access MVP
    > >
    > > Dannyboy1001 wrote:
    > > > In the first part, 2-6 characters, the second part 2-5 characters, the third
    > > > part 2-3 characters and there is an optional 4th part which can be any
    > > > length, but isn't used very often. Now you know everything I know...which in
    > > > my case isn't much...lol.
    > > >

    > > Steve, thanks a million....it works. The only thing I need to do is put an IIF stattement in the expression so any blank fields don't diaplay as an #ERROR, but think I can handle that.....again Thanks you're the best :) I can now start analyizing our alerts by vendor and see who is having issues.
     

Share This Page