Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011

    Unanswered: Splitting a field from a linked table

    Hi all,

    I am using a link table to pull some people related data from one of our systems, I am then using access to compare this with some other data.

    I have a problem because the data I am pulling comes across in a bit of an odd format. The data relates to activities across a day such as meetings, 121s, dealing with emails etc.

    The format it comes in is as below (all within a single field):

    The line break is represented by a square symbol. The format means:
    • Activity Type Code; Activity Code; Start Time; End Time; Original to shift Code.

    The last item 'original to shift' is largely irrelevant to me.
    There can be any number of these lines within the field depending on the amount an individual has in a day (I've seen a maximum around 20). I've managed to split this in Excel using a million formulas to break it up but it seem to me that it should be easier in Access.

    Does anyone have any clues as to how to split this easily?


  2. #2
    Join Date
    Apr 2011
    In VBA, there is the Split() function that excels at things like this.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you could split this in excel as well if you wished,. although there are differences between VBA for Access and VBA fro Excel they are simialr enough

    I think you will actually have to run split multipile times
    one to split the incoming datastream into individual lines.
    the 'square' symbol you've identified is the EoL or EoR symbol, you should be able to split the incoming stream into an Array of 'lines', forget if its vbcr, chr$(13), chr$(10) it may even be another symbol
    having split the datastream into 'lines', then split each 'line' into values. you could do that as a function
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2011
    cheers both, i'll try and figure it out although my VB is pretty poor so will likely be back.

    Chr 10 seems to work in the replace function in access, hopefully it will work the same in the split in VB.

  5. #5
    Join Date
    Apr 2011
    If you have lots and manipulations like this to do, you will gain a lot by getting your feet wet in VBA.

    While the Split function allows you to create an array of elements based on a string containing a given separator, the function Replace("MyString",Find,Replace,[Other options]) allows you to make such replacements.

Posting Permissions

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