# Thread: using Mid, Left, Right, INStrRev (Access 2003)

1. ## 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. ## 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)

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

Here is a solution that doesn't use VBA code:
<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. ## 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. ## 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. ## 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. ## 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
•