Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Function to elimante words o seprated after dush

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

  1. ldiaz

    ldiaz
    Expand Collapse
    Guest

    Hi, I have a Table with information like this,

    Name_Size
    FELSEN 1272-LL/LX-1L
    FRACA 2031-LR/LSX+2
    GALLADORA 2288-LR/LSX+2

    but now I want to separare it in two column

    Name Size
    FELSEN 1272- LL/LX-1L
    FRACA 2031- LR/LSX+2
    GALLADORA 2288- LR/LSX+2

    I want it sperated after of the dash.
    I know that I need to run an SQL update but I don't how separate it.

    could you help me on this please.



    --
    Lorenzo Díaz
    Cad Technician
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
    "-") - 1).

    What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > Hi, I have a Table with information like this,
    >
    > Name_Size
    > FELSEN 1272-LL/LX-1L
    > FRACA 2031-LR/LSX+2
    > GALLADORA 2288-LR/LSX+2
    >
    > but now I want to separare it in two column
    >
    > Name Size
    > FELSEN 1272- LL/LX-1L
    > FRACA 2031- LR/LSX+2
    > GALLADORA 2288- LR/LSX+2
    >
    > I want it sperated after of the dash.
    > I know that I need to run an SQL update but I don't how separate it.
    >
    > could you help me on this please.
    >
    >
    >
    > --
    > Lorenzo Díaz
    > Cad Technician
     
  4. ldiaz

    ldiaz
    Expand Collapse
    Guest

    Hi Douglas,

    thank for your answer ,I have a problem writting the Update statement, could
    you help me please.




    --
    Lorenzo Díaz
    Cad Technician


    "Douglas J Steele" wrote:

    > What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
    > "-") - 1).
    >
    > What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") + 1).
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > > Hi, I have a Table with information like this,
    > >
    > > Name_Size
    > > FELSEN 1272-LL/LX-1L
    > > FRACA 2031-LR/LSX+2
    > > GALLADORA 2288-LR/LSX+2
    > >
    > > but now I want to separare it in two column
    > >
    > > Name Size
    > > FELSEN 1272- LL/LX-1L
    > > FRACA 2031- LR/LSX+2
    > > GALLADORA 2288- LR/LSX+2
    > >
    > > I want it sperated after of the dash.
    > > I know that I need to run an SQL update but I don't how separate it.
    > >
    > > could you help me on this please.
    > >
    > >
    > >
    > > --
    > > Lorenzo Díaz
    > > Cad Technician

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    UPDATE MyTable
    SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    WHERE InStr([Name_Size], "-") > 0

    Note that Name and Size are not good names for fields in tables: I believe
    they're both reserve words.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    > Hi Douglas,
    >
    > thank for your answer ,I have a problem writting the Update statement,

    could
    > you help me please.
    >
    >
    >
    >
    > --
    > Lorenzo Díaz
    > Cad Technician
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
    > > "-") - 1).
    > >
    > > What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") +

    1).
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > > > Hi, I have a Table with information like this,
    > > >
    > > > Name_Size
    > > > FELSEN 1272-LL/LX-1L
    > > > FRACA 2031-LR/LSX+2
    > > > GALLADORA 2288-LR/LSX+2
    > > >
    > > > but now I want to separare it in two column
    > > >
    > > > Name Size
    > > > FELSEN 1272- LL/LX-1L
    > > > FRACA 2031- LR/LSX+2
    > > > GALLADORA 2288- LR/LSX+2
    > > >
    > > > I want it sperated after of the dash.
    > > > I know that I need to run an SQL update but I don't how separate it.
    > > >
    > > > could you help me on this please.
    > > >
    > > >
    > > >
    > > > --
    > > > Lorenzo Díaz
    > > > Cad Technician

    > >
    > >
    > >
     
  6. ldiaz

    ldiaz
    Expand Collapse
    Guest

    here is my code and it does not work.


    DoCmd.RunSQL "UPDATE Size_tbl"
    Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    WHERE InStr([Name_Size], "-") > 1


    do you know why?

    the field are as Text in the table.
    and I named as Size1 and Name1
    Thanks
    ld
    --
    Lorenzo Díaz
    Cad Technician


    "Douglas J Steele" wrote:

    > UPDATE MyTable
    > SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    > [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > WHERE InStr([Name_Size], "-") > 0
    >
    > Note that Name and Size are not good names for fields in tables: I believe
    > they're both reserve words.
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    > > Hi Douglas,
    > >
    > > thank for your answer ,I have a problem writting the Update statement,

    > could
    > > you help me please.
    > >
    > >
    > >
    > >
    > > --
    > > Lorenzo Díaz
    > > Cad Technician
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > What's in front of the dash will be Left([Name_Size], InStr([Name_Size],
    > > > "-") - 1).
    > > >
    > > > What's after the dash will be Mid([Name_Size], InStr([Name_Size], "-") +

    > 1).
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > > > > Hi, I have a Table with information like this,
    > > > >
    > > > > Name_Size
    > > > > FELSEN 1272-LL/LX-1L
    > > > > FRACA 2031-LR/LSX+2
    > > > > GALLADORA 2288-LR/LSX+2
    > > > >
    > > > > but now I want to separare it in two column
    > > > >
    > > > > Name Size
    > > > > FELSEN 1272- LL/LX-1L
    > > > > FRACA 2031- LR/LSX+2
    > > > > GALLADORA 2288- LR/LSX+2
    > > > >
    > > > > I want it sperated after of the dash.
    > > > > I know that I need to run an SQL update but I don't how separate it.
    > > > >
    > > > > could you help me on this please.
    > > > >
    > > > >
    > > > >
    > > > > --
    > > > > Lorenzo Díaz
    > > > > Cad Technician
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    DoCmd.RunSQL "UPDATE Size_tbl" & _
    "Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    "WHERE InStr([Name_Size], "-") > 1"

    Hopefully that'll survive word-wrap...


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    news:DADC03BB-E15C-4B58-9D0A-E0CE5775A834@microsoft.com...
    > here is my code and it does not work.
    >
    >
    > DoCmd.RunSQL "UPDATE Size_tbl"
    > Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    > [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > WHERE InStr([Name_Size], "-") > 1
    >
    >
    > do you know why?
    >
    > the field are as Text in the table.
    > and I named as Size1 and Name1
    > Thanks
    > ld
    > --
    > Lorenzo Díaz
    > Cad Technician
    >
    >
    > "Douglas J Steele" wrote:
    >
    >> UPDATE MyTable
    >> SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    >> [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    >> WHERE InStr([Name_Size], "-") > 0
    >>
    >> Note that Name and Size are not good names for fields in tables: I
    >> believe
    >> they're both reserve words.
    >>
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >> news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    >> > Hi Douglas,
    >> >
    >> > thank for your answer ,I have a problem writting the Update statement,

    >> could
    >> > you help me please.
    >> >
    >> >
    >> >
    >> >
    >> > --
    >> > Lorenzo Díaz
    >> > Cad Technician
    >> >
    >> >
    >> > "Douglas J Steele" wrote:
    >> >
    >> > > What's in front of the dash will be Left([Name_Size],
    >> > > InStr([Name_Size],
    >> > > "-") - 1).
    >> > >
    >> > > What's after the dash will be Mid([Name_Size], InStr([Name_Size],
    >> > > "-") +

    >> 1).
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >> > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    >> > > > Hi, I have a Table with information like this,
    >> > > >
    >> > > > Name_Size
    >> > > > FELSEN 1272-LL/LX-1L
    >> > > > FRACA 2031-LR/LSX+2
    >> > > > GALLADORA 2288-LR/LSX+2
    >> > > >
    >> > > > but now I want to separare it in two column
    >> > > >
    >> > > > Name Size
    >> > > > FELSEN 1272- LL/LX-1L
    >> > > > FRACA 2031- LR/LSX+2
    >> > > > GALLADORA 2288- LR/LSX+2
    >> > > >
    >> > > > I want it sperated after of the dash.
    >> > > > I know that I need to run an SQL update but I don't how separate
    >> > > > it.
    >> > > >
    >> > > > could you help me on this please.
    >> > > >
    >> > > >
    >> > > >
    >> > > > --
    >> > > > Lorenzo Díaz
    >> > > > Cad Technician
    >> > >
    >> > >
    >> > >

    >>
    >>
    >>
     
  8. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Oops. Just noticed there's a space missing between the name of the table and
    the SET keyword. It should be:

    DoCmd.RunSQL "UPDATE Size_tbl " & _
    "SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    "WHERE InStr([Name_Size], "-") > 1"



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:uOM71KCjGHA.4044@TK2MSFTNGP03.phx.gbl...
    > DoCmd.RunSQL "UPDATE Size_tbl" & _
    > "Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    > "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    > "WHERE InStr([Name_Size], "-") > 1"
    >
    > Hopefully that'll survive word-wrap...
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > news:DADC03BB-E15C-4B58-9D0A-E0CE5775A834@microsoft.com...
    >> here is my code and it does not work.
    >>
    >>
    >> DoCmd.RunSQL "UPDATE Size_tbl"
    >> Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    >> [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    >> WHERE InStr([Name_Size], "-") > 1
    >>
    >>
    >> do you know why?
    >>
    >> the field are as Text in the table.
    >> and I named as Size1 and Name1
    >> Thanks
    >> ld
    >> --
    >> Lorenzo Díaz
    >> Cad Technician
    >>
    >>
    >> "Douglas J Steele" wrote:
    >>
    >>> UPDATE MyTable
    >>> SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    >>> [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    >>> WHERE InStr([Name_Size], "-") > 0
    >>>
    >>> Note that Name and Size are not good names for fields in tables: I
    >>> believe
    >>> they're both reserve words.
    >>>
    >>>
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no e-mails, please!)
    >>>
    >>>
    >>> "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >>> news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    >>> > Hi Douglas,
    >>> >
    >>> > thank for your answer ,I have a problem writting the Update statement,
    >>> could
    >>> > you help me please.
    >>> >
    >>> >
    >>> >
    >>> >
    >>> > --
    >>> > Lorenzo Díaz
    >>> > Cad Technician
    >>> >
    >>> >
    >>> > "Douglas J Steele" wrote:
    >>> >
    >>> > > What's in front of the dash will be Left([Name_Size],
    >>> > > InStr([Name_Size],
    >>> > > "-") - 1).
    >>> > >
    >>> > > What's after the dash will be Mid([Name_Size], InStr([Name_Size],
    >>> > > "-") +
    >>> 1).
    >>> > >
    >>> > > --
    >>> > > Doug Steele, Microsoft Access MVP
    >>> > > http://I.Am/DougSteele
    >>> > > (no e-mails, please!)
    >>> > >
    >>> > >
    >>> > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >>> > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    >>> > > > Hi, I have a Table with information like this,
    >>> > > >
    >>> > > > Name_Size
    >>> > > > FELSEN 1272-LL/LX-1L
    >>> > > > FRACA 2031-LR/LSX+2
    >>> > > > GALLADORA 2288-LR/LSX+2
    >>> > > >
    >>> > > > but now I want to separare it in two column
    >>> > > >
    >>> > > > Name Size
    >>> > > > FELSEN 1272- LL/LX-1L
    >>> > > > FRACA 2031- LR/LSX+2
    >>> > > > GALLADORA 2288- LR/LSX+2
    >>> > > >
    >>> > > > I want it sperated after of the dash.
    >>> > > > I know that I need to run an SQL update but I don't how separate
    >>> > > > it.
    >>> > > >
    >>> > > > could you help me on this please.
    >>> > > >
    >>> > > >
    >>> > > >
    >>> > > > --
    >>> > > > Lorenzo Díaz
    >>> > > > Cad Technician
    >>> > >
    >>> > >
    >>> > >
    >>>
    >>>
    >>>

    >
    >
     
  9. ldiaz

    ldiaz
    Expand Collapse
    Guest

    Hi Douglas, Thank you so much for your help, now it;s working fine ..

    I had a problem with the criteria, but I have omitted that step,

    Thank you again.

    I have ckicked YES in:
    Did this post answer the question?

    --
    Lorenzo Díaz
    Cad Technician


    "Douglas J. Steele" wrote:

    > Oops. Just noticed there's a space missing between the name of the table and
    > the SET keyword. It should be:
    >
    > DoCmd.RunSQL "UPDATE Size_tbl " & _
    > "SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    > "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    > "WHERE InStr([Name_Size], "-") > 1"
    >
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:uOM71KCjGHA.4044@TK2MSFTNGP03.phx.gbl...
    > > DoCmd.RunSQL "UPDATE Size_tbl" & _
    > > "Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    > > "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    > > "WHERE InStr([Name_Size], "-") > 1"
    > >
    > > Hopefully that'll survive word-wrap...
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > > news:DADC03BB-E15C-4B58-9D0A-E0CE5775A834@microsoft.com...
    > >> here is my code and it does not work.
    > >>
    > >>
    > >> DoCmd.RunSQL "UPDATE Size_tbl"
    > >> Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    > >> [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > >> WHERE InStr([Name_Size], "-") > 1
    > >>
    > >>
    > >> do you know why?
    > >>
    > >> the field are as Text in the table.
    > >> and I named as Size1 and Name1
    > >> Thanks
    > >> ld
    > >> --
    > >> Lorenzo Díaz
    > >> Cad Technician
    > >>
    > >>
    > >> "Douglas J Steele" wrote:
    > >>
    > >>> UPDATE MyTable
    > >>> SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    > >>> [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > >>> WHERE InStr([Name_Size], "-") > 0
    > >>>
    > >>> Note that Name and Size are not good names for fields in tables: I
    > >>> believe
    > >>> they're both reserve words.
    > >>>
    > >>>
    > >>> --
    > >>> Doug Steele, Microsoft Access MVP
    > >>> http://I.Am/DougSteele
    > >>> (no e-mails, please!)
    > >>>
    > >>>
    > >>> "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > >>> news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    > >>> > Hi Douglas,
    > >>> >
    > >>> > thank for your answer ,I have a problem writting the Update statement,
    > >>> could
    > >>> > you help me please.
    > >>> >
    > >>> >
    > >>> >
    > >>> >
    > >>> > --
    > >>> > Lorenzo Díaz
    > >>> > Cad Technician
    > >>> >
    > >>> >
    > >>> > "Douglas J Steele" wrote:
    > >>> >
    > >>> > > What's in front of the dash will be Left([Name_Size],
    > >>> > > InStr([Name_Size],
    > >>> > > "-") - 1).
    > >>> > >
    > >>> > > What's after the dash will be Mid([Name_Size], InStr([Name_Size],
    > >>> > > "-") +
    > >>> 1).
    > >>> > >
    > >>> > > --
    > >>> > > Doug Steele, Microsoft Access MVP
    > >>> > > http://I.Am/DougSteele
    > >>> > > (no e-mails, please!)
    > >>> > >
    > >>> > >
    > >>> > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > >>> > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > >>> > > > Hi, I have a Table with information like this,
    > >>> > > >
    > >>> > > > Name_Size
    > >>> > > > FELSEN 1272-LL/LX-1L
    > >>> > > > FRACA 2031-LR/LSX+2
    > >>> > > > GALLADORA 2288-LR/LSX+2
    > >>> > > >
    > >>> > > > but now I want to separare it in two column
    > >>> > > >
    > >>> > > > Name Size
    > >>> > > > FELSEN 1272- LL/LX-1L
    > >>> > > > FRACA 2031- LR/LSX+2
    > >>> > > > GALLADORA 2288- LR/LSX+2
    > >>> > > >
    > >>> > > > I want it sperated after of the dash.
    > >>> > > > I know that I need to run an SQL update but I don't how separate
    > >>> > > > it.
    > >>> > > >
    > >>> > > > could you help me on this please.
    > >>> > > >
    > >>> > > >
    > >>> > > >
    > >>> > > > --
    > >>> > > > Lorenzo Díaz
    > >>> > > > Cad Technician
    > >>> > >
    > >>> > >
    > >>> > >
    > >>>
    > >>>
    > >>>

    > >
    > >

    >
    >
    >
     
  10. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Sorry about that: the last line should have been:

    "WHERE InStr([Name_Size], '-') > 1"

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    news:B413918D-EC6C-41BB-B49B-8AD2463F4DD8@microsoft.com...
    > Hi Douglas, Thank you so much for your help, now it;s working fine ..
    >
    > I had a problem with the criteria, but I have omitted that step,
    >
    > Thank you again.
    >
    > I have ckicked YES in:
    > Did this post answer the question?
    >
    > --
    > Lorenzo Díaz
    > Cad Technician
    >
    >
    > "Douglas J. Steele" wrote:
    >
    >> Oops. Just noticed there's a space missing between the name of the table
    >> and
    >> the SET keyword. It should be:
    >>
    >> DoCmd.RunSQL "UPDATE Size_tbl " & _
    >> "SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    >> "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    >> "WHERE InStr([Name_Size], "-") > 1"
    >>
    >>
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:uOM71KCjGHA.4044@TK2MSFTNGP03.phx.gbl...
    >> > DoCmd.RunSQL "UPDATE Size_tbl" & _
    >> > "Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    >> > "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    >> > "WHERE InStr([Name_Size], "-") > 1"
    >> >
    >> > Hopefully that'll survive word-wrap...
    >> >
    >> >
    >> > --
    >> > Doug Steele, Microsoft Access MVP
    >> > http://I.Am/DougSteele
    >> > (no private e-mails, please)
    >> >
    >> >
    >> > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >> > news:DADC03BB-E15C-4B58-9D0A-E0CE5775A834@microsoft.com...
    >> >> here is my code and it does not work.
    >> >>
    >> >>
    >> >> DoCmd.RunSQL "UPDATE Size_tbl"
    >> >> Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    >> >> [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    >> >> WHERE InStr([Name_Size], "-") > 1
    >> >>
    >> >>
    >> >> do you know why?
    >> >>
    >> >> the field are as Text in the table.
    >> >> and I named as Size1 and Name1
    >> >> Thanks
    >> >> ld
    >> >> --
    >> >> Lorenzo Díaz
    >> >> Cad Technician
    >> >>
    >> >>
    >> >> "Douglas J Steele" wrote:
    >> >>
    >> >>> UPDATE MyTable
    >> >>> SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    >> >>> [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    >> >>> WHERE InStr([Name_Size], "-") > 0
    >> >>>
    >> >>> Note that Name and Size are not good names for fields in tables: I
    >> >>> believe
    >> >>> they're both reserve words.
    >> >>>
    >> >>>
    >> >>> --
    >> >>> Doug Steele, Microsoft Access MVP
    >> >>> http://I.Am/DougSteele
    >> >>> (no e-mails, please!)
    >> >>>
    >> >>>
    >> >>> "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >> >>> news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    >> >>> > Hi Douglas,
    >> >>> >
    >> >>> > thank for your answer ,I have a problem writting the Update
    >> >>> > statement,
    >> >>> could
    >> >>> > you help me please.
    >> >>> >
    >> >>> >
    >> >>> >
    >> >>> >
    >> >>> > --
    >> >>> > Lorenzo Díaz
    >> >>> > Cad Technician
    >> >>> >
    >> >>> >
    >> >>> > "Douglas J Steele" wrote:
    >> >>> >
    >> >>> > > What's in front of the dash will be Left([Name_Size],
    >> >>> > > InStr([Name_Size],
    >> >>> > > "-") - 1).
    >> >>> > >
    >> >>> > > What's after the dash will be Mid([Name_Size], InStr([Name_Size],
    >> >>> > > "-") +
    >> >>> 1).
    >> >>> > >
    >> >>> > > --
    >> >>> > > Doug Steele, Microsoft Access MVP
    >> >>> > > http://I.Am/DougSteele
    >> >>> > > (no e-mails, please!)
    >> >>> > >
    >> >>> > >
    >> >>> > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    >> >>> > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    >> >>> > > > Hi, I have a Table with information like this,
    >> >>> > > >
    >> >>> > > > Name_Size
    >> >>> > > > FELSEN 1272-LL/LX-1L
    >> >>> > > > FRACA 2031-LR/LSX+2
    >> >>> > > > GALLADORA 2288-LR/LSX+2
    >> >>> > > >
    >> >>> > > > but now I want to separare it in two column
    >> >>> > > >
    >> >>> > > > Name Size
    >> >>> > > > FELSEN 1272- LL/LX-1L
    >> >>> > > > FRACA 2031- LR/LSX+2
    >> >>> > > > GALLADORA 2288- LR/LSX+2
    >> >>> > > >
    >> >>> > > > I want it sperated after of the dash.
    >> >>> > > > I know that I need to run an SQL update but I don't how
    >> >>> > > > separate
    >> >>> > > > it.
    >> >>> > > >
    >> >>> > > > could you help me on this please.
    >> >>> > > >
    >> >>> > > >
    >> >>> > > >
    >> >>> > > > --
    >> >>> > > > Lorenzo Díaz
    >> >>> > > > Cad Technician
    >> >>> > >
    >> >>> > >
    >> >>> > >
    >> >>>
    >> >>>
    >> >>>
    >> >
    >> >

    >>
    >>
    >>
     
  11. ldiaz

    ldiaz
    Expand Collapse
    Guest

    Yes, that works very fine.

    Thank you for your support.
    --
    Lorenzo Díaz
    Cad Technician


    "Douglas J. Steele" wrote:

    > Sorry about that: the last line should have been:
    >
    > "WHERE InStr([Name_Size], '-') > 1"
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > news:B413918D-EC6C-41BB-B49B-8AD2463F4DD8@microsoft.com...
    > > Hi Douglas, Thank you so much for your help, now it;s working fine ..
    > >
    > > I had a problem with the criteria, but I have omitted that step,
    > >
    > > Thank you again.
    > >
    > > I have ckicked YES in:
    > > Did this post answer the question?
    > >
    > > --
    > > Lorenzo Díaz
    > > Cad Technician
    > >
    > >
    > > "Douglas J. Steele" wrote:
    > >
    > >> Oops. Just noticed there's a space missing between the name of the table
    > >> and
    > >> the SET keyword. It should be:
    > >>
    > >> DoCmd.RunSQL "UPDATE Size_tbl " & _
    > >> "SET [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    > >> "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    > >> "WHERE InStr([Name_Size], "-") > 1"
    > >>
    > >>
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no private e-mails, please)
    > >>
    > >>
    > >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > >> news:uOM71KCjGHA.4044@TK2MSFTNGP03.phx.gbl...
    > >> > DoCmd.RunSQL "UPDATE Size_tbl" & _
    > >> > "Set [Name1] = Left([Name_Size], InStr([Name_Size], '-') - 1), " & _
    > >> > "[Size1] = Mid([Name_Size], InStr([Name_Size], '-') + 1) " & _
    > >> > "WHERE InStr([Name_Size], "-") > 1"
    > >> >
    > >> > Hopefully that'll survive word-wrap...
    > >> >
    > >> >
    > >> > --
    > >> > Doug Steele, Microsoft Access MVP
    > >> > http://I.Am/DougSteele
    > >> > (no private e-mails, please)
    > >> >
    > >> >
    > >> > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > >> > news:DADC03BB-E15C-4B58-9D0A-E0CE5775A834@microsoft.com...
    > >> >> here is my code and it does not work.
    > >> >>
    > >> >>
    > >> >> DoCmd.RunSQL "UPDATE Size_tbl"
    > >> >> Set [Name1] = Left([Name_Size], InStr([Name_Size], "-") - 1)
    > >> >> [Size1] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > >> >> WHERE InStr([Name_Size], "-") > 1
    > >> >>
    > >> >>
    > >> >> do you know why?
    > >> >>
    > >> >> the field are as Text in the table.
    > >> >> and I named as Size1 and Name1
    > >> >> Thanks
    > >> >> ld
    > >> >> --
    > >> >> Lorenzo Díaz
    > >> >> Cad Technician
    > >> >>
    > >> >>
    > >> >> "Douglas J Steele" wrote:
    > >> >>
    > >> >>> UPDATE MyTable
    > >> >>> SET [Name] = Left([Name_Size], InStr([Name_Size],"-") - 1),
    > >> >>> [Size] = Mid([Name_Size], InStr([Name_Size], "-") + 1)
    > >> >>> WHERE InStr([Name_Size], "-") > 0
    > >> >>>
    > >> >>> Note that Name and Size are not good names for fields in tables: I
    > >> >>> believe
    > >> >>> they're both reserve words.
    > >> >>>
    > >> >>>
    > >> >>> --
    > >> >>> Doug Steele, Microsoft Access MVP
    > >> >>> http://I.Am/DougSteele
    > >> >>> (no e-mails, please!)
    > >> >>>
    > >> >>>
    > >> >>> "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > >> >>> news:436677F5-52DB-464C-A583-55FBF77B8073@microsoft.com...
    > >> >>> > Hi Douglas,
    > >> >>> >
    > >> >>> > thank for your answer ,I have a problem writting the Update
    > >> >>> > statement,
    > >> >>> could
    > >> >>> > you help me please.
    > >> >>> >
    > >> >>> >
    > >> >>> >
    > >> >>> >
    > >> >>> > --
    > >> >>> > Lorenzo Díaz
    > >> >>> > Cad Technician
    > >> >>> >
    > >> >>> >
    > >> >>> > "Douglas J Steele" wrote:
    > >> >>> >
    > >> >>> > > What's in front of the dash will be Left([Name_Size],
    > >> >>> > > InStr([Name_Size],
    > >> >>> > > "-") - 1).
    > >> >>> > >
    > >> >>> > > What's after the dash will be Mid([Name_Size], InStr([Name_Size],
    > >> >>> > > "-") +
    > >> >>> 1).
    > >> >>> > >
    > >> >>> > > --
    > >> >>> > > Doug Steele, Microsoft Access MVP
    > >> >>> > > http://I.Am/DougSteele
    > >> >>> > > (no e-mails, please!)
    > >> >>> > >
    > >> >>> > >
    > >> >>> > > "ldiaz" <ldiaz@discussions.microsoft.com> wrote in message
    > >> >>> > > news:52D6888C-15CE-4504-902E-1DDE63BFC2E3@microsoft.com...
    > >> >>> > > > Hi, I have a Table with information like this,
    > >> >>> > > >
    > >> >>> > > > Name_Size
    > >> >>> > > > FELSEN 1272-LL/LX-1L
    > >> >>> > > > FRACA 2031-LR/LSX+2
    > >> >>> > > > GALLADORA 2288-LR/LSX+2
    > >> >>> > > >
    > >> >>> > > > but now I want to separare it in two column
    > >> >>> > > >
    > >> >>> > > > Name Size
    > >> >>> > > > FELSEN 1272- LL/LX-1L
    > >> >>> > > > FRACA 2031- LR/LSX+2
    > >> >>> > > > GALLADORA 2288- LR/LSX+2
    > >> >>> > > >
    > >> >>> > > > I want it sperated after of the dash.
    > >> >>> > > > I know that I need to run an SQL update but I don't how
    > >> >>> > > > separate
    > >> >>> > > > it.
    > >> >>> > > >
    > >> >>> > > > could you help me on this please.
    > >> >>> > > >
    > >> >>> > > >
    > >> >>> > > >
    > >> >>> > > > --
    > >> >>> > > > Lorenzo Díaz
    > >> >>> > > > Cad Technician
    > >> >>> > >
    > >> >>> > >
    > >> >>> > >
    > >> >>>
    > >> >>>
    > >> >>>
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page