Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Extra Characters From Bar Code Scan (2000)

    Field service reps have to maintain a car stock inventory. Currently, we are emailed an Excel document (by part number) of what the powers that be say we have in our vehicles. The part number is usually 6 or 7 characters long but I don't know if that is a hard and fast rule. We then match the part part numbers of the items we have against the Excel spread sheet annotating what we have and what we don't have but are supposed to have. Long, slow, tedious process.

    My plan is to import that spread sheet into Access and then zap the bar code labels into another table, run an unmatched query which should display all items we are supposed to have but did not zap (which means we don't have them). So far, so good - I think. The problem I've run into is the bar codes have extra characters that are not part of the part number. From what I can tell, there are always 6 extra characters at the beginning of the number and 3 extra characters at the end.

    For example, the part number is 773834 and the bar code returns *+H6287738341L*

    I tried using Right([Part#],9) to strip the leading characters but that's when I discovered not all part numbers are the same length so that doesn't work right all the time. Is it possible to somehow strip the first six characters then skip to the end, count backwards by three and then strip the last three characters? (Or vice versa?)

    Thanks for the help.

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

    Re: Removing Extra Characters From Bar Code Scan (2000)

    Try
    <code>
    Mid([Part#],7,Len([Part#])-9)
    </code>
    This extracts the part <img src=/S/pun.gif border=0 alt=pun width=22 height=18> of the string that starts at the 7th character (thus omitting the first 6 characters) and is 9 characters shorter than the entire string (thus omitting an additional 3 characters at the end)

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Extra Characters From Bar Code Scan (2000)

    Thanks, Hans. That works beautifully!

    Now that that works, it exposed a flaw in my unmatched query but I'll ask about that in a new post since it's unrelated to this problem.

Posting Permissions

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