Can I use a Find expression to parse data in a Table

[gregu710]

Hi, I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:


=LEFT(B6,(FIND("*",B6)))

You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. Any ideas on how to recreate that functionality in a table in Access?

[John_G]

You can use the instr() function. Instr returns the first occurrance of one string within another.

Example - if MyString = "ABCD*EFGH", then Instr(MyString,"*") = 5

From that, left(MyString,Instr(MyString,"*")-1) = "ABCD"

John

[gregu710]

You can use the instr() function. Instr returns the first occurrance of one string within another.

Example - if MyString = "ABCD*EFGH", then Instr(MyString,"*") = 5

From that, left(MyString,Instr(MyString,"*")-1) = "ABCD"

John

Thanks John, got it worked out where I'm not getting synax errors or anything, but it keeps giving me the following result: #Type! Does it matter if the data I am seeking on the left side of the "*" is all alpha, all numeric or alpha-numeric (or a mix of alpha-numeric and numeric)?

[John_G]

HI -

If you are using it as a string, then no, there should not be an issue. In what context are you getting the #Type error?

John

[gregu710]

never mind John, got it solved, and even tweaked it to cut off the "*".... Thanks again! To clarify, I didn't have the start position listed before the MyString in the statement Instr(Mystring,"*",1). Once I changed it to Instr(1,Mystring,"*",1) and it worked. And to eliminate the "*" from the result field I modified the Left command as follows: Left(Mystring,(Instr(1,Mystring,"*",1)-1))