String Search

[crimsntyd]

I'm dealing with part numbers in my database that have two formats. For example:

Format 1:
285W0024-1
285W0024-1B
285W0024-10B
285W0024-100

Format 2:
69-37350-1
69-37350-1B
69-37350-10B
69-37350-100

When I query my database, I'm using instr to compare these numbers to the part number the user has already selected from a list. However, in the user list, I don't include the alphanumeric characters at the end. This is because I want to grab all data for the part number, but only to the dash NUMBER level, thus getting all data for all part numbers ending in A,B,C,etc. The problem with this is I'm also grabbing all the 10's (and 100's) when I ask for the 1's, all the 20's when I ask for the 2's, etc. What I'd like to do is create a query that gets rid of the alphanumerics at the end of the part number and stores THAT number in a separate column. Any ideas?

I'm using Access 2002 and VB 6. Thanks in advance...

 

[evalesthy]

I think thread701-1142476 might help you out.

 

[Remou]

Hi
How about:
[tt]strString = "69-37350-10B"
strString = Mid(strString, 1, InStrRev(strString, "-") - 1)[/tt]

 

[cbsm]

Maybe using the wildchar ([], *, -,) might help you ?
Instead of "instr" - use "like reference & [A-Z]" (or something in that direction.

 

[crimsntyd]

Thanks, everyone. I was able to get around it using a combination of InstrRev and Len.