find 6 digits in text field.

[marcus.]

Hi,

I have a text field, which along with notes should
comprise a six digit number. I want to create an new
field in my query which returns this six digit number.

How do i do this?

Example Table:

SEE 579676
RETURNED WITH 701366
ret via mailinv 694584
ret to stockinv 611388
REPLACES CLAIM 606305 'W'
REI 706105 INV 706105 C2
Invoice610687
Inv678243 7Ctns PUP redir
INV 787676 30/3 RR

Any ideas?

Thanks,
Marcus

 

[Jeff Boyce]

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

 

[Guest]

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[John Spencer (MVP)]

What version of ACCESS? With 2000 and later you could use the Split function.
UNTESTED AIRCODE follows

Public Function GetSix(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim strReturn As String

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
If varObject(iLoop) Like "######" Then
strReturn = varObject(iLoop)
Exit For
End If
Next iLoop

GetSix = strReturn

End Function

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks,

Wow, your programming looks like poetry when compared to my sloppy slog.. Yes I am using Access 2000 so your code works like a dream. Except that the split function doesn't always work as the 6 digits sometimes do not have a space on either side. I have the following, which seems to get around this, but are struggling to get it to work :

Public Function eOrderLookup(inputText) As String
'Purpose: find six digits (numercial values) in row and returns them.
'Arguments: text field for function to look up.
'Author: Marcus.
'Examples:
'1. To find the eorder in the text, e.g.:
' EOrderLookup("B1 INV785574 26/03/04")
' Returns: "785574"
Dim myCheck As String
Dim x As Integer
Dim y As Integer
Dim myEOrder As String

For x = 1 To Len(inputText)
myCheck = ""
For y = 0 To 5
myCheck = myCheck & IsNumeric(Mid(inputText, x + y, 1))
If myCheck = "TrueTrueTrueTrueTrueTrue" Then
myEOrder = Mid(inputText, x, 6) ' 6 numeric fields in a row!
eOrderLookup = myEOrder
End
End If
Next y
Next x
eOrderLookup = myEOrder
End Function

This function works when I call an i test it:
Function Test()
Dim myText As String
myText = "B1 INV785574 26/03/04"
eOrderLookup myText
End Function

But if i add it to a query the i get the following error: "Undefined Function 'eOrderLookup' in expressoin"

I'm not sure why...

thanks again for you help
marcus.

What version of ACCESS? With 2000 and later you could use the Split function.
UNTESTED AIRCODE follows

Public Function GetSix(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim strReturn As String

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
If varObject(iLoop) Like "######" Then
strReturn = varObject(iLoop)
Exit For
End If
Next iLoop

GetSix = strReturn

End Function

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks,

Wow, your programming looks like poetry when compared to my sloppy slog.. Yes I am using Access 2000 so your code works like a dream. Except that the split function doesn't always work as the 6 digits sometimes do not have a space on either side. I have the following, which seems to get around this, but are struggling to get it to work :

Public Function eOrderLookup(inputText) As String
'Purpose: find six digits (numercial values) in row and returns them.
'Arguments: text field for function to look up.
'Author: Marcus.
'Examples:
'1. To find the eorder in the text, e.g.:
' EOrderLookup("B1 INV785574 26/03/04")
' Returns: "785574"
Dim myCheck As String
Dim x As Integer
Dim y As Integer
Dim myEOrder As String

For x = 1 To Len(inputText)
myCheck = ""
For y = 0 To 5
myCheck = myCheck & IsNumeric(Mid(inputText, x + y, 1))
If myCheck = "TrueTrueTrueTrueTrueTrue" Then
myEOrder = Mid(inputText, x, 6) ' 6 numeric fields in a row!
eOrderLookup = myEOrder
End
End If
Next y
Next x
eOrderLookup = myEOrder
End Function

This function works when I call an i test it:
Function Test()
Dim myText As String
myText = "B1 INV785574 26/03/04"
eOrderLookup myText
End Function

But if i add it to a query the i get the following error: "Undefined Function 'eOrderLookup' in expressoin"

I'm not sure why...

thanks again for you help
marcus.

What version of ACCESS? With 2000 and later you could use the Split function.
UNTESTED AIRCODE follows

Public Function GetSix(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim strReturn As String

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
If varObject(iLoop) Like "######" Then
strReturn = varObject(iLoop)
Exit For
End If
Next iLoop

GetSix = strReturn

End Function

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks,

Wow, your programming looks like poetry when compared to my sloppy slog.. Yes I am using Access 2000 so your code works like a dream. Except that the split function doesn't always work as the 6 digits sometimes do not have a space on either side. I have the following, which seems to get around this, but are struggling to get it to work :

Public Function eOrderLookup(inputText) As String
'Purpose: find six digits (numercial values) in row and returns them.
'Arguments: text field for function to look up.
'Author: Marcus.
'Examples:
'1. To find the eorder in the text, e.g.:
' EOrderLookup("B1 INV785574 26/03/04")
' Returns: "785574"
Dim myCheck As String
Dim x As Integer
Dim y As Integer
Dim myEOrder As String

For x = 1 To Len(inputText)
myCheck = ""
For y = 0 To 5
myCheck = myCheck & IsNumeric(Mid(inputText, x + y, 1))
If myCheck = "TrueTrueTrueTrueTrueTrue" Then
myEOrder = Mid(inputText, x, 6) ' 6 numeric fields in a row!
eOrderLookup = myEOrder
End
End If
Next y
Next x
eOrderLookup = myEOrder
End Function

This function works when I call an i test it:
Function Test()
Dim myText As String
myText = "B1 INV785574 26/03/04"
eOrderLookup myText
End Function

But if i add it to a query the i get the following error: "Undefined Function 'eOrderLookup' in expressoin"

I'm not sure why...

thanks again for you help
marcus.

What version of ACCESS? With 2000 and later you could use the Split function.
UNTESTED AIRCODE follows

Public Function GetSix(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim strReturn As String

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
If varObject(iLoop) Like "######" Then
strReturn = varObject(iLoop)
Exit For
End If
Next iLoop

GetSix = strReturn

End Function

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks,

Wow, your programming looks like poetry when compared to my sloppy slog.. Yes I am using Access 2000 so your code works like a dream. Except that the split function doesn't always work as the 6 digits sometimes do not have a space on either side. I have the following, which seems to get around this, but are struggling to get it to work :

Public Function eOrderLookup(inputText) As String
'Purpose: find six digits (numercial values) in row and returns them.
'Arguments: text field for function to look up.
'Author: Marcus.
'Examples:
'1. To find the eorder in the text, e.g.:
' EOrderLookup("B1 INV785574 26/03/04")
' Returns: "785574"
Dim myCheck As String
Dim x As Integer
Dim y As Integer
Dim myEOrder As String

For x = 1 To Len(inputText)
myCheck = ""
For y = 0 To 5
myCheck = myCheck & IsNumeric(Mid(inputText, x + y, 1))
If myCheck = "TrueTrueTrueTrueTrueTrue" Then
myEOrder = Mid(inputText, x, 6) ' 6 numeric fields in a row!
eOrderLookup = myEOrder
End
End If
Next y
Next x
eOrderLookup = myEOrder
End Function

This function works when I call an i test it:
Function Test()
Dim myText As String
myText = "B1 INV785574 26/03/04"
eOrderLookup myText
End Function

But if i add it to a query the i get the following error: "Undefined Function 'eOrderLookup' in expressoin"

I'm not sure why...

thanks again for you help
marcus.

What version of ACCESS? With 2000 and later you could use the Split function.
UNTESTED AIRCODE follows

Public Function GetSix(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim strReturn As String

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
If varObject(iLoop) Like "######" Then
strReturn = varObject(iLoop)
Exit For
End If
Next iLoop

GetSix = strReturn

End Function

Thanks Jeff,

I tried the val() function, but didn't have much luck, while it did calculate for a 'few' rows. The majority of the data returned with zeros.

Does anyone else have any ideas?

My thought is perhaps i should be writing a macro that checks the field character by character through a loop. If there is six characters in a row, then to return that value and move to the next filed, else leave a blank. But how to i set out to do this?

Thanks,
Marcus

Marcus

Up until your last example,

INV 787676 30/3 RR

an option would have been to use the Val() function to return the numeric
value.

Given the possibility that more than one "set" of numbers may be in the text
string, Val([YourString]) isn't guaranteed to pick the correct set of
numbers.

Still, consider using Val() and inspecting the results to help you identify
which records you'd need to "manually" fix.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

sorry about the multiple replies above, the web browser was coming up with a page saying there was a system fault and for me to try again later...

obiously not the case,


marcus.

 

[Guest]

This works (not at all poetic!):

Public Function GetSixB(StrIN) As String
Dim varObject As Variant
Dim iLoop As Integer
Dim myEOrder As String
Dim x as Integer
Dim y as Integer

varObject = Split(StrIN, " ")

For iLoop = LBound(varObject) To UBound(varObject)
For x = 1 To Len(varObject(iLoop))
myCheck = ""
For y = 0 To 5
myCheck = myCheck & IsNumeric(Mid(varObject(iLoop), x + y, 1))
If myCheck = "TrueTrueTrueTrueTrueTrue" Then
myEOrder = Mid(varObject(iLoop), x, 6) ' 6 numeric fields in a row!
' MsgBox "Found It : " & myEOrder
GetSixB = myEOrder
Exit For 'y
End If
Next y
Next x

Next iLoop

GetSixB = myEOrder

End Function