Left Funtion with 2 static characters (1 Viewer)

Hello World,

I have to query a record with 2 of the same static characters. "-"

I can get the left function with the first " but I can't get the rest up to the second "-"

Example: B-4352B-PXP02W01-10

TagNo: Left([EventInstanceID],InStr([EventInstanceID],"-")) Brings me the first char plus "-" The next section is needed also but the remainder is not.

Needed: B-4352B

Is there a Mid Function that I could add to go to the second chaacter instead of stopping at the first one?

Thank you

There a Function named Mid() in MS Access which allows you to extract characters from within a target string. There is also a Function named Instr() that will allow you to find specific characters within a target string. Combining these two should get you what you are searching for.

Use Google or Bing to look them up and get back with any questions.

-- Rookie

The InStr() function has a Start argument that you're not using.

It's time for you to write your own custom extraction function. Ultimately you will be using Mid (http://www.techonthenet.com/access/functions/string/mid.php) and Instr (http://www.techonthenet.com/access/functions/string/instr.php).

Mid takes 3 arguments a string (S) to work on, a character position (CS1) to start at and a character position (CS2) to end at.

You will pass this custom function S and it will return the part of the string you want. In the function you must calculate CS1 and CS2.

CS1 will be 1 because you are starting from the beginning of S.
Then you set CS2 equal to the position of the first hyphen using Instr.
Then you make a substring of S from CS2 to the end of the string.
Then you recalculate CS2 adding to it the position of the hyphen in that substring (which is the 2nd overall hyphen)
With that you now know your starting and ending points and can extract what you want from S.

Thank you for your reply.

The numbers calculation is the only option that works.

The problem I am having is the information between dashes is not always the same number of characters. some times it has an A,B, or C at the end and sometimes not.. I was looking for a formula that read something like Left to second "-" or maybe left to first dash, like I have it and then left again to the second dash using a mid function.

Left([EventInstanceID],InStr([EventInstanceID],"-"))

is there a way to go InStr(InStr to get me into the second dash after I passed the first one? I guess that is why I am looking for a Mid Function to add to the one I have.

Thanks

If there is always a single letter before the first dash, go back to post 3. If it varies by much, you'll have to go to a more complex method. Plog's is one way, one InStr() within a second InStr() is another.

I was looking for a formula that read something like Left to second "-"

Click to expand...

That's the exact methodology I illustrated in my prior post. You pass a function a string, it uses InStr and Mid to find the exact position of that second dash, with which you can then extract the sbustring you desire.