Welcome to SPN

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

Sign Up Now!

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
    > >> >>> > >
    > >> >>> > >
    > >> >>> > >
    > >> >>>
    > >> >>>
    > >> >>>
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page