Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using Mid, Left, Right, INStrRev (Access 2003)

    I am having the worst time with parsing data in an access field.

    If I have the following number:

    12345 67890 55555

    and previously received help to get (Mid([UPCNumber],InStrRev([UPCNumber]," ")+1)) for the last set of numbers (55555).

    How do I use Left and/or Right function in conjunction with InStr to get the first set and middle set?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    I'm assuming the 3 groups of numbers don't always have the same # of digits each time. The only "constant" being they are separated by a space.

    dim spc1 as integer
    dim spc2 as integer
    dim source as string
    dim leftset as string
    dim middleset as string
    dim rightset as string

    source="12345 67890 55555" (obviously you'd fill this some other way)
    spc1 = instr(source," ")
    spc2= instrrev(source," ")

    leftset = left(source,spc1-1
    middleset = mid(source,spc1 + 1, spc2 - spc1 -1)
    rightset= mid(source,spc2 + 1)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    Here is a solution that doesn't use VBA code:
    In your query, add the following fields:
    <code>
    P1: InStr([UPCNumber]," ")

    P2: InStrRev([UPCNumber]," ")

    LeftPart: Left([UPCNumber],[P1]-1)

    MiddlePart: Mid([UPCNumber],[P1]+1,[P2]-[P1]-1)

    RightPart: Mid([UPCNumber],[P2]+1)
    </code>
    Note: you must define P1 and P2 before (= to the left of) LeftPart etc.

  4. #4
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    Hans, thanks for your help. I wanted to see if I could make an update query for all the fields so that the parse applies to a table (just out of interest). I had the following:

    Update to first table field:
    Left([UPCNumber],InStr([UPCNumber]," ")-1)

    Update to second table field:
    Mid([UPCNumber],InStr([UPCNumber]," ")+1,InStrRev([UPCNumber]," ")-InStr([UPCNumber]," ")-1)

    Update to third table field:
    Mid([UPCNumber],InStrRev([UPCNumber]," ")+1,3)

    But each time I ran it, it told me zero records would be updated. The table fields were blank. It seems like I cannot define the P1 and P2 within the update statement. I did have the issue number one working from a previous post you helped me with, but I dont understand what is different about these statements together. All three had "Is Null" and the table fields were blank. And all had the same table listed.

    Can you tell me what is wrong with these statements in an update query?

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

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    Are you sure you put these expressions in the Update to line, and not in the Criteria line?
    If you had Is Null in the criteria line, are you sure that the fields are really null, and not "", for example?

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    You were right.

    When I first started to play around with this before I ever posted, Ihad run one succesful update query of this nature and it worked. Thereafter, no update ever gave me anything except "Zero Records". Apparently, the fields were blank, but not "Null" after all. So my last question is, for testing purposes, how would I get the fields to be really "Null"?

    Thanks for all your help! I learned more about field parsing and queries than I ever knew.

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

    Re: using Mid, Left, Right, INStrRev (Access 2003)

    You could try an update query with "" in the Criteria line and Null in the Update to line.

Posting Permissions

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