Welcome to SPN

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

Sign Up Now!

Import and manipulate data records into access

Discussion in 'Information Technology' started by Santa-D, Jul 28, 2006.

  1. Santa-D

    Santa-D
    Expand Collapse
    Guest

    I've got an excel sheet that I need to import into a table, however, I
    want the function to automatically manipulate the data prior to it
    being imported.

    For example, i have a field called [Flt No] and the format is 16107 for

    example, when the VBA function is running I want it to add "EA" to the
    record so it becomes EA16107 but the field I want it to import into is
    called [ORDNO]


    another field in the same dataset I want to import is [Product], this
    field has many different product descriptions which I want to
    manipulate prior to being imported.


    I want to convert the following:


    Premium UL - PULP
    ULP - ULP
    Ultimate - PULP
    Unleaded Motor Spirit - ULP
    LPG - LPG
    Autogas - LPG
    Monthly AD - Monthly AD


    Any help would be wonderful, ps: I'm still an amateur in VBA
     
  2. Loading...

    Similar Threads Forum Date
    Importance Of Dastar History of Sikhism Aug 12, 2016
    Importance Of Tirath / Pilgrimage As Per Gurbani Gurmat Vichaar Jun 1, 2016
    Superheroes: Arjan Bhullar Explains Wrestling's Importance In The Sikh Community Sports & Fitness Apr 15, 2016
    Important decisions in life Questions and Answers May 25, 2015
    Friendship Between Genders... Important Question Questions and Answers Dec 20, 2012

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    It would be far easier to first import the data, then manipulate is with a
    series of update queries. For instance the first update query would look
    something like:

    UPDATE tblMyData SET tblMyData.ORDNO= "EA" & [ORDNO]
    WHERE (((tblMyData.ORDNO) Is Not Null And Left([ORDNO],2)<>"EA"));
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Santa-D" <stevenn@forgone.org> wrote in message
    news:1150096281.000121.240670@f6g2000cwb.googlegroups.com...
    > I've got an excel sheet that I need to import into a table, however, I
    > want the function to automatically manipulate the data prior to it
    > being imported.
    >
    > For example, i have a field called [Flt No] and the format is 16107 for
    >
    > example, when the VBA function is running I want it to add "EA" to the
    > record so it becomes EA16107 but the field I want it to import into is
    > called [ORDNO]
    >
    >
    > another field in the same dataset I want to import is [Product], this
    > field has many different product descriptions which I want to
    > manipulate prior to being imported.
    >
    >
    > I want to convert the following:
    >
    >
    > Premium UL - PULP
    > ULP - ULP
    > Ultimate - PULP
    > Unleaded Motor Spirit - ULP
    > LPG - LPG
    > Autogas - LPG
    > Monthly AD - Monthly AD
    >
    >
    > Any help would be wonderful, ps: I'm still an amateur in VBA
    >
     
  4. Santa-D

    Santa-D
    Expand Collapse
    Guest

    Hi Arvin,

    Thank you for your response.
    I'll give it a go and see how I'll go and let you know of the outcome.


    Arvin Meyer [MVP] wrote:
    > It would be far easier to first import the data, then manipulate is with a
    > series of update queries. For instance the first update query would look
    > something like:
    >
    > UPDATE tblMyData SET tblMyData.ORDNO= "EA" & [ORDNO]
    > WHERE (((tblMyData.ORDNO) Is Not Null And Left([ORDNO],2)<>"EA"));
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    > "Santa-D" <stevenn@forgone.org> wrote in message
    > news:1150096281.000121.240670@f6g2000cwb.googlegroups.com...
    > > I've got an excel sheet that I need to import into a table, however, I
    > > want the function to automatically manipulate the data prior to it
    > > being imported.
    > >
    > > For example, i have a field called [Flt No] and the format is 16107 for
    > >
    > > example, when the VBA function is running I want it to add "EA" to the
    > > record so it becomes EA16107 but the field I want it to import into is
    > > called [ORDNO]
    > >
    > >
    > > another field in the same dataset I want to import is [Product], this
    > > field has many different product descriptions which I want to
    > > manipulate prior to being imported.
    > >
    > >
    > > I want to convert the following:
    > >
    > >
    > > Premium UL - PULP
    > > ULP - ULP
    > > Ultimate - PULP
    > > Unleaded Motor Spirit - ULP
    > > LPG - LPG
    > > Autogas - LPG
    > > Monthly AD - Monthly AD
    > >
    > >
    > > Any help would be wonderful, ps: I'm still an amateur in VBA
    > >
     

Share This Page