I have a field that contains text and a 4 digit number. the 4 digit number is
not always in the same position in the field. What I am trying to do is
locate the number in the field and then copy the number only to a new field.
The one problem is. The record could look like this " I would like 3434 to be
part" or it could look like this "I would like3434 to be part".
This is been driving me nuts for a while...
Thanks for any help

This assumes that the text does not contain any other digits ...

Public Function DigitsFromText(ByVal TextIn As String) As String

Const strcDigits As String = "0123456789"

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To Len(TextIn)
strChar = Mid$(TextIn, lngLoop, 1)
If InStr(1, strcDigits, strChar) > 0 Then
strWork = strWork & strChar
End If
Next lngLoop

DigitsFromText = strWork

End Function

--
Brendan Reynolds
Access MVP

"The Chomp" <(E-Mail Removed)> wrote in message
news:542E662A-E445-473E-AE1F-(E-Mail Removed)...
>I have a field that contains text and a 4 digit number. the 4 digit number
>is
> not always in the same position in the field. What I am trying to do is
> locate the number in the field and then copy the number only to a new
> field.
> The one problem is. The record could look like this " I would like 3434 to
> be
> part" or it could look like this "I would like3434 to be part".
> This is been driving me nuts for a while...
> Thanks for any help
>
>
>

Thanks Brendan,

The text may contain more digits but I would only be after the first
grouping of 4 digits.

I will give this a try.

Thanks again

"Brendan Reynolds" wrote:

>
> This assumes that the text does not contain any other digits ...
>
> Public Function DigitsFromText(ByVal TextIn As String) As String
>
> Const strcDigits As String = "0123456789"
>
> Dim strWork As String
> Dim lngLoop As Long
> Dim strChar As String
>
> For lngLoop = 1 To Len(TextIn)
> strChar = Mid$(TextIn, lngLoop, 1)
> If InStr(1, strcDigits, strChar) > 0 Then
> strWork = strWork & strChar
> End If
> Next lngLoop
>
> DigitsFromText = strWork
>
> End Function
>
> --
> Brendan Reynolds
> Access MVP
>
> "The Chomp" <(E-Mail Removed)> wrote in message
> news:542E662A-E445-473E-AE1F-(E-Mail Removed)...
> >I have a field that contains text and a 4 digit number. the 4 digit number
> >is
> > not always in the same position in the field. What I am trying to do is
> > locate the number in the field and then copy the number only to a new
> > field.
> > The one problem is. The record could look like this " I would like 3434 to
> > be
> > part" or it could look like this "I would like3434 to be part".
> > This is been driving me nuts for a while...
> > Thanks for any help
> >
> >
> >
>
>
>

Good Afternoon.

This worked great. I diod run into a weeee bit of a problem. It seems that
there is can be more that one set of numbers in the field (just can't get
people to enter the right stuff).
After a review of all the records in the field, it seems that the only
number I need worry about is the "left most" number.
Anyone have anything that would help.

Thanks all

"The Chomp" wrote:

> I have a field that contains text and a 4 digit number. the 4 digit number is
> not always in the same position in the field. What I am trying to do is
> locate the number in the field and then copy the number only to a new field.
> The one problem is. The record could look like this " I would like 3434 to be
> part" or it could look like this "I would like3434 to be part".
> This is been driving me nuts for a while...
> Thanks for any help
>
>
>

You could try the following modification to the original code.
Public Function DigitsFromText(ByVal TextIn As String) As String

Const strcDigits As String = "0123456789"

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To Len(TextIn)
strChar = Mid$(TextIn, lngLoop, 1)
If InStr(1, strcDigits, strChar) > 0 Then
strWork = strWork & strChar
'Got 4 consecutive number characters so stop
If Len(strWork) = 4 Then Exit For
Else
'Clear strWork if the digits aren't consecutive
strWork = vbnullstring
End If

Next lngLoop

DigitsFromText = strWork

End Function
"The Chomp" <(E-Mail Removed)> wrote in message
news24EB2A8-5B1B-4A8C-B8A7-(E-Mail Removed)...
> Good Afternoon.
>
> This worked great. I diod run into a weeee bit of a problem. It seems that
> there is can be more that one set of numbers in the field (just can't get
> people to enter the right stuff).
> After a review of all the records in the field, it seems that the only
> number I need worry about is the "left most" number.
> Anyone have anything that would help.
>
> Thanks all
>
> "The Chomp" wrote:
>
>> I have a field that contains text and a 4 digit number. the 4 digit
>> number is
>> not always in the same position in the field. What I am trying to do is
>> locate the number in the field and then copy the number only to a new
>> field.
>> The one problem is. The record could look like this " I would like 3434
>> to be
>> part" or it could look like this "I would like3434 to be part".
>> This is been driving me nuts for a while...
>> Thanks for any help
>>
>>
>>

One approach is to use a regular expression to identify the first group
of 4 digits in the string. If you copy the rgxExtract function at
http://www.j.nurick.dial.pipex.com/C...rgxExtract.htm into a
code module, you can use something like this, where XXX is a variable
(or in a query, the field) containing the text:

rgxExtract(XXX, "\d{4}")

\d means a digit 0-9, and {4} means four of them, so the expression gets
the first four contiguous digits in the string. E.g. if you give it "xxx
12345 xxx678 xxx9999xxx" it will return "1234".

If necessary one can get much more subtle. For instance this

rgxExtract(XXX, "^(?:.*?\D)?(\d{4})\D")

will get the first substring of exactly 4 digits (in the example above
it skips the 12345 and 678 and returns "9999").


On Tue, 2 May 2006 11:40:02 -0700, The Chomp
<(E-Mail Removed)> wrote:

>Good Afternoon.
>
>This worked great. I diod run into a weeee bit of a problem. It seems that
>there is can be more that one set of numbers in the field (just can't get
>people to enter the right stuff).
>After a review of all the records in the field, it seems that the only
>number I need worry about is the "left most" number.
>Anyone have anything that would help.
>
> Thanks all
>
>"The Chomp" wrote:
>
>> I have a field that contains text and a 4 digit number. the 4 digit number is
>> not always in the same position in the field. What I am trying to do is
>> locate the number in the field and then copy the number only to a new field.
>> The one problem is. The record could look like this " I would like 3434 to be
>> part" or it could look like this "I would like3434 to be part".
>> This is been driving me nuts for a while...
>> Thanks for any help
>>
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thanks all. These worked great. Now if I can get the people who enter the
data to do it correctly.



"John Nurick" wrote:

> One approach is to use a regular expression to identify the first group
> of 4 digits in the string. If you copy the rgxExtract function at
> http://www.j.nurick.dial.pipex.com/C...rgxExtract.htm into a
> code module, you can use something like this, where XXX is a variable
> (or in a query, the field) containing the text:
>
> rgxExtract(XXX, "\d{4}")
>
> \d means a digit 0-9, and {4} means four of them, so the expression gets
> the first four contiguous digits in the string. E.g. if you give it "xxx
> 12345 xxx678 xxx9999xxx" it will return "1234".
>
> If necessary one can get much more subtle. For instance this
>
> rgxExtract(XXX, "^(?:.*?\D)?(\d{4})\D")
>
> will get the first substring of exactly 4 digits (in the example above
> it skips the 12345 and 678 and returns "9999").
>
>
> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
> <(E-Mail Removed)> wrote:
>
> >Good Afternoon.
> >
> >This worked great. I diod run into a weeee bit of a problem. It seems that
> >there is can be more that one set of numbers in the field (just can't get
> >people to enter the right stuff).
> >After a review of all the records in the field, it seems that the only
> >number I need worry about is the "left most" number.
> >Anyone have anything that would help.
> >
> > Thanks all
> >
> >"The Chomp" wrote:
> >
> >> I have a field that contains text and a 4 digit number. the 4 digit number is
> >> not always in the same position in the field. What I am trying to do is
> >> locate the number in the field and then copy the number only to a new field.
> >> The one problem is. The record could look like this " I would like 3434 to be
> >> part" or it could look like this "I would like3434 to be part".
> >> This is been driving me nuts for a while...
> >> Thanks for any help
> >>
> >>
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

That's much more difficult<g>. Can you split up this field so they enter
the 4-digit number in one place and the text next to it? That would make
it comparatively easy to validate the number.



On Thu, 4 May 2006 03:07:01 -0700, The Chomp
<(E-Mail Removed)> wrote:

>Thanks all. These worked great. Now if I can get the people who enter the
>data to do it correctly.
>
>
>
>"John Nurick" wrote:
>
>> One approach is to use a regular expression to identify the first group
>> of 4 digits in the string. If you copy the rgxExtract function at
>> http://www.j.nurick.dial.pipex.com/C...rgxExtract.htm into a
>> code module, you can use something like this, where XXX is a variable
>> (or in a query, the field) containing the text:
>>
>> rgxExtract(XXX, "\d{4}")
>>
>> \d means a digit 0-9, and {4} means four of them, so the expression gets
>> the first four contiguous digits in the string. E.g. if you give it "xxx
>> 12345 xxx678 xxx9999xxx" it will return "1234".
>>
>> If necessary one can get much more subtle. For instance this
>>
>> rgxExtract(XXX, "^(?:.*?\D)?(\d{4})\D")
>>
>> will get the first substring of exactly 4 digits (in the example above
>> it skips the 12345 and 678 and returns "9999").
>>
>>
>> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
>> <(E-Mail Removed)> wrote:
>>
>> >Good Afternoon.
>> >
>> >This worked great. I diod run into a weeee bit of a problem. It seems that
>> >there is can be more that one set of numbers in the field (just can't get
>> >people to enter the right stuff).
>> >After a review of all the records in the field, it seems that the only
>> >number I need worry about is the "left most" number.
>> >Anyone have anything that would help.
>> >
>> > Thanks all
>> >
>> >"The Chomp" wrote:
>> >
>> >> I have a field that contains text and a 4 digit number. the 4 digit number is
>> >> not always in the same position in the field. What I am trying to do is
>> >> locate the number in the field and then copy the number only to a new field.
>> >> The one problem is. The record could look like this " I would like 3434 to be
>> >> part" or it could look like this "I would like3434 to be part".
>> >> This is been driving me nuts for a while...
>> >> Thanks for any help
>> >>
>> >>
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

I have tried to get people to at least stick to a standard when entering a
number. I asked that the 4 digit number be the "first then a space". For some
reason not everyone can figure this out.
As for splitting the text field, the text field is the "descriptionfield "
of a change action request (transport) in SAP. This is linked to third party
software for migrating managing change action requests.

The Chomp


"John Nurick" wrote:

> That's much more difficult<g>. Can you split up this field so they enter
> the 4-digit number in one place and the text next to it? That would make
> it comparatively easy to validate the number.
>
>
>
> On Thu, 4 May 2006 03:07:01 -0700, The Chomp
> <(E-Mail Removed)> wrote:
>
> >Thanks all. These worked great. Now if I can get the people who enter the
> >data to do it correctly.
> >
> >
> >
> >"John Nurick" wrote:
> >
> >> One approach is to use a regular expression to identify the first group
> >> of 4 digits in the string. If you copy the rgxExtract function at
> >> http://www.j.nurick.dial.pipex.com/C...rgxExtract.htm into a
> >> code module, you can use something like this, where XXX is a variable
> >> (or in a query, the field) containing the text:
> >>
> >> rgxExtract(XXX, "\d{4}")
> >>
> >> \d means a digit 0-9, and {4} means four of them, so the expression gets
> >> the first four contiguous digits in the string. E.g. if you give it "xxx
> >> 12345 xxx678 xxx9999xxx" it will return "1234".
> >>
> >> If necessary one can get much more subtle. For instance this
> >>
> >> rgxExtract(XXX, "^(?:.*?\D)?(\d{4})\D")
> >>
> >> will get the first substring of exactly 4 digits (in the example above
> >> it skips the 12345 and 678 and returns "9999").
> >>
> >>
> >> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
> >> <(E-Mail Removed)> wrote:
> >>
> >> >Good Afternoon.
> >> >
> >> >This worked great. I diod run into a weeee bit of a problem. It seems that
> >> >there is can be more that one set of numbers in the field (just can't get
> >> >people to enter the right stuff).
> >> >After a review of all the records in the field, it seems that the only
> >> >number I need worry about is the "left most" number.
> >> >Anyone have anything that would help.
> >> >
> >> > Thanks all
> >> >
> >> >"The Chomp" wrote:
> >> >
> >> >> I have a field that contains text and a 4 digit number. the 4 digit number is
> >> >> not always in the same position in the field. What I am trying to do is
> >> >> locate the number in the field and then copy the number only to a new field.
> >> >> The one problem is. The record could look like this " I would like 3434 to be
> >> >> part" or it could look like this "I would like3434 to be part".
> >> >> This is been driving me nuts for a while...
> >> >> Thanks for any help
> >> >>
> >> >>
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>