Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Extract and Apply Data to Column (2000/2003)

    Ummm. Where the dots were in the column names I had table1 in Square brackets I'm assuming, theyve been chopped by HTML.

    Sorry.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Extract and Apply Data to Column (2000/2003)

    Edited by HansV to restore <!t>[Table1]<!/t>

    Hi,

    Hoping there's an easy solution to this one:

    Stated Simply I've got an alpha numeric Identifier in one Column "XT0000000000" (Where the 0s are numerical) where I need to Extract from the 3rd Character to the 11th Character (so we chop off the XT and the last Digit) and then apply them to another column as 9 digits of text (it can have a leading zero which is important), for the purposes of the exercise the Columns are named:

    <!t>[Table1].[IDENTIFIER1] It can contain other content than the XT prefix but we only want to process the XTs (so to speak)
    <!t>[Table1].[IDENTIFIER2] Must be Null to Apply the Data (contents not to be over written)

    I already run code via a Macro at export of a file so a Sub routines a preference, and yes much to my shame we don't use a form for entry.

    Any help as Always gratefully received.

    Thanks Guys in Advance.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract and Apply Data to Column (2000/2003)

    Create a query based on the table.
    Add the Identifier1 and Identifier2 fields to the query grid.
    Set the criteria for Identifier 1 to
    <code>
    Like "XT*"
    </code>
    Set the criteria for Identifier2 to
    <code>
    Is Null
    </code>
    Select Query | Update Query.
    Enter the following expression in the Update to line for Identifier2:
    <code>
    Mid([Identifier1],3,9)
    </code>
    Save the query.
    You can run it by double clicking it from the database window, or you can open it from a macro or VBA code, using the OpenQuery action.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Extract and Apply Data to Column (2000/2003)

    Yes of Course.... Thats easier than coding the solution....

    Thanks Hansv

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •