I need to test a field to determine if it has a 7 digit number combination that begins with a "10", 20", or "80' and if it does, return that 7 digit number combination. A function to do this would be great. Is this possible?

Assumic your input text box is named "CombNum" then the code would be :

If IsNumber(Me![CombNum]) Then
If Len(Me![CombNum]) = 7 and (Cstr(Left(Me![CombNum],2)) = "10" Or Cstr(Left(Me![CombNum],2)) = "20" Or Cstr(Left(Me![CombNum],2)) = "80") Then
MsgBox "You Win"
Else
MsgBox "You Lose"
End If
Else
MsgBox "Invalied Number"
End If

I tired to make this work for me but no luck. This will primarily be used in queries and the field will be of variable length. The 7 digit combination can be anywhere in the field.

putting it like that its an intirely different question!

what kind of data is stored in this variable field ? could you give an example ?

Same data in a field (3 examples):
PO# 284523ABR34 / SO# 1087321
BCD3345,1087321
17534

Here is a function I found from a programmer "willybgw" that looks for a 10 character combination beginning with 2 letters followed by 8 digits that works great for finding PO numbers. To get it to find my Sales Order numbers in the 7 digit format I have been trying to modify it to look at 3 different combinations rather than just the one combination it is desinged for. I don't know if this is possible or better to start from scratch.
---------
Public Function FindAndCapturePO(infield As String) As String

Dim X As Integer
Dim CNT As Integer
Dim TARGET As String
CNT = Len(infield) - 9
X = 0
Start:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If
TARGET = Mid(infield, X, 10)
If TARGET Like "*[A-Z][A-Z]########*" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
ENDIT:
End Function
-------------

try this , i'm not famillier with such technique of coding but here goes :


Public Function FindAndCapturePO(infield As String) As String

Dim X As Integer
Dim CNT As Integer
Dim TARGET As String
CNT = Len(infield) - 6
X = 0
Start:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If
TARGET = Mid(infield, X, 7)
If TARGET Like "10#####" Or TARGET Like "20#####" Or TARGET Like "80#####" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
ENDIT:
End Function

That did it! Thank you so much!

heh , i just researched , the above technique goes way back to the time of QBasic , my thats old :) anyway you are welcome

Could I bother you for one more segment?
I need to look for delimiters after the combination. The data seems to be separated by many different delimiters. Any thoughts?

would you just visualize your fantacies in an example ?

Data examples:
SO#1087692/PO#KK10345678
1087692, KK102345678
ABC10934321/1087692,693,694
As you can see in each line there can be more than one number starting with 10 that would be found it simply looked for 7 digits starting with 10. However, using the delimiters found at the end of the 7 digit combination (or the end of the string itself) we can be more accurate in finding the true 7 digit combination we seek.

which of these are you looking up ?

It can be any of them but we can use the third one as an example.
I want to get back 1087692 (without the ,) and not 1093432

and why not 1093432 nore 1034567 ?

My aplolgies for not bein clear. Each line is a separate data field.
All our Sales Order Numbers are 7 digits in length and begin with a 10, 20 or 80.
The data field contains information from the shipper and can contain any information the shipper chooses to insert, including data that can contain numbers that look like our Sales Orders but are usually longer or shorter in lenght. We normally find that the shipper will usually separate the different data elements with some sort of delimiter so if we can focus on a 7 digit number that has either a delimiter at the end of the 7 digits or the field ends after the 7th digit we can get a higher hit rate.

As a

but the a kk401012345/ would still be a hit , i would suggest looking for something starting SO# better ? or is it inconsistant of them to type it ?

I wish it were that simple. We are the recipients of whatever data they enter so we can not count on always having a "SO#" or something else as the prefix. We expect to get some false hits but are counting on the majority being good.:)

Public Function FindAndCapturePO(infield As String) As String

Dim X As Integer
Dim CNT As Integer
Dim TARGET As String
CNT = Len(infield) - 6
X = 0

Start:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If

If X = 1 or X = (CNT - 1) Then
TARGET = Mid(infield, X, 8)
If TARGET Like "10#####[!1-9]" Or TARGET Like "20#####[!1-9]" Or TARGET Like "80#####[1-9]" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
Else
TARGET = Mid(infield, X, 9)
If TARGET Like "[!1-9]10#####[!1-9]" Or TARGET Like "[!1-9]20#####[!1-9]" Or TARGET Like "[1-9]80#####[1-9]" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
End If

ENDIT:
End Function


PS : This Code Keeps Getting Weirder By The Minute

Well....It because those look like serial numbers for components to Nuclear Warheads and he if he told you, then he would have to kill you. :D

In any case....Use the Split function.

.

I finally got it all to work. Thanks for all your help NightMayor!:)

You are welcome , be kind to the world please :)