help required

  • Thread starter Thread starter BDCC
  • Start date Start date
B

BDCC

Is there a way of searching a field for a certain text string and then
pulling the next 11 characters into a new field during a query?
 
Add this function to a code module.

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind ,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")
 
I tried to put this in but it comes up with the following error 'Compile
error user defined type not defined' on the line 'Dim regEx As New RegExp'.
Any ideas?


HSalim said:
Here is the answer you are looking for:

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions 5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your questions to the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be cheeky and ask
you to refine the function slightly. The field I'm searching through
contains all manner of characters including line fields etc. In the eleven
character string it finds can it filter out all characters except numbers?

Any help would be appreciated.

Thanks

HSalim said:
Add this function to a code module.

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind ,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")
Click to expand...
Click to expand...
 
If you had read the code you would have seen



BDCC said:
I tried to put this in but it comes up with the following error 'Compile
error user defined type not defined' on the line 'Dim regEx As New RegExp'.
Any ideas?


HSalim said:
Here is the answer you are looking for:

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions 5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your questions to the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be cheeky and ask
you to refine the function slightly. The field I'm searching through
contains all manner of characters including line fields etc. In the eleven
character string it finds can it filter out all characters except numbers?

Any help would be appreciated.

Thanks

HSalim said:
Add this function to a code module.

Public Function Next11(StringToSearch As String, StringToFind As
Click to expand...
String)
As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind ,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")




Is there a way of searching a field for a certain text string and then
pulling the next 11 characters into a new field during a query?
Click to expand...
Click to expand...
Click to expand...
 
I need a slight alteration to the code. Once it has got the string of
numbers, I now need it to remove all preceeding 0's (zero's), how can this
be done??

thanks



BDCC said:
Sorry being a bit thick. I'm new to Access, VB etc, how do you do this??

HSalim said:
If you had read the code you would have seen
' You Must add a reference to Microsoft VBScript Regular Expressions
Click to expand...
5.5




BDCC said:
I tried to put this in but it comes up with the following error 'Compile
error user defined type not defined' on the line 'Dim regEx As New RegExp'.
Any ideas?


Here is the answer you are looking for:

Public Function Next11(StringToSearch As String, StringToFind As String)
As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions 5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down
earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your questions to
the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be cheeky
Click to expand...
and
ask
you to refine the function slightly. The field I'm searching through
contains all manner of characters including line fields etc. In the
eleven
character string it finds can it filter out all characters except numbers?

Any help would be appreciated.

Thanks

Add this function to a code module.

Public Function Next11(StringToSearch As String, StringToFind As String)
As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind ,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")




Is there a way of searching a field for a certain text string
Click to expand...
and
then
pulling the next 11 characters into a new field during a query?
Click to expand...
Click to expand...
Click to expand...
 
Try Next11 = CLng(RetVal)

--
Brendan Reynolds (Access MVP)
(e-mail address removed)

BDCC said:
Gives an error on the line 'Next11 = RetVal'. Help!!



HSalim said:
Change the return value's type from String to Long. i.e. change to
Public Function Next11(StringToSearch As String, StringToFind As String)
As Long
This will automatically strip the leading zeros

good luck




BDCC said:
I need a slight alteration to the code. Once it has got the string of
numbers, I now need it to remove all preceeding 0's (zero's), how can this
be done??

thanks



Sorry being a bit thick. I'm new to Access, VB etc, how do you do this??

If you had read the code you would have seen
' You Must add a reference to Microsoft VBScript Regular Expressions
5.5




I tried to put this in but it comes up with the following error
'Compile
error user defined type not defined' on the line 'Dim regEx As New
RegExp'.
Any ideas?


Here is the answer you are looking for:

Public Function Next11(StringToSearch As String, StringToFind As
String)
As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions
5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first
match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been
Click to expand...
cut
down
earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your questions
to
the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be cheeky
and
ask
you to refine the function slightly. The field I'm searching
through
contains all manner of characters including line fields etc.
Click to expand...
In
the
eleven
character string it finds can it filter out all characters except
numbers?

Any help would be appreciated.

Thanks

Add this function to a code module.

Public Function Next11(StringToSearch As String,
Click to expand...
Click to expand...
StringToFind
As
Click to expand...
Click to expand...
 
It's getting a bit confusing reading back through the previous posts and
trying to picture what the code looks like at this stage. Could you post the
code as it is now, and indicate on which line it breaks?

--
Brendan Reynolds (Access MVP)
(e-mail address removed)

BDCC said:
It didn't work, gives the error 'type mismatch', any more ideas?


Brendan Reynolds (MVP) said:
Try Next11 = CLng(RetVal)
Click to expand...
string
of
numbers, I now need it to remove all preceeding 0's (zero's), how can
this
be done??

thanks



Sorry being a bit thick. I'm new to Access, VB etc, how do you do
this??

If you had read the code you would have seen
' You Must add a reference to Microsoft VBScript Regular
Expressions
5.5




I tried to put this in but it comes up with the following error
'Compile
error user defined type not defined' on the line 'Dim regEx
Click to expand...
As
New
RegExp'.
Any ideas?


Here is the answer you are looking for:

Public Function Next11(StringToSearch As String,
Click to expand...
StringToFind
As
String)
As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular
Expressions
5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the
first
match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has
Click to expand...
been
cut
down
earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your
questions
to
the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be
cheeky
and
ask
you to refine the function slightly. The field I'm searching
through
contains all manner of characters including line fields
Click to expand...
etc.
In
the
eleven
character string it finds can it filter out all characters
except
numbers?

Any help would be appreciated.

Thanks

Add this function to a code module.

Public Function Next11(StringToSearch As String, StringToFind
As
String)
As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind
,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")




Is there a way of searching a field for a certain text
string
and
then
pulling the next 11 characters into a new field during a
query?
Click to expand...
Click to expand...
Click to expand...
 
I just tried it and did not get a type mismstch. I did get an overflow
error though.
Since you are interested in a max of 11 numbers, you can use the currency
data type or a double


Anyway, here is the function again.

next11("dkdkb0001234512345rkr","kb") returns 12345123

HS

----------------------------
Public Function Next11(StringToSearch As String, StringToFind As String) As
Currency

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions 5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier
RetVal = (Left(Nums, 11))


Next11 = CCur(RetVal)

End Function
-----------------------------






Brendan Reynolds (MVP) said:
It's getting a bit confusing reading back through the previous posts and
trying to picture what the code looks like at this stage. Could you post the
code as it is now, and indicate on which line it breaks?

--
Brendan Reynolds (Access MVP)
(e-mail address removed)

BDCC said:
It didn't work, gives the error 'type mismatch', any more ideas?


string how
can
Click to expand...
regEx
As
New
RegExp'.
Any ideas?


Here is the answer you are looking for:

Public Function Next11(StringToSearch As String, StringToFind
As
String)
As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string
Click to expand...
to
find
'retval = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular
Expressions
5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds
Click to expand...
only
the
first
match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been
cut
down
earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function
-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your
questions
to
the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can I be
cheeky
and
ask
you to refine the function slightly. The field I'm searching
through
contains all manner of characters including line fields etc.
In
the
eleven
character string it finds can it filter out all characters
except
numbers?

Any help would be appreciated.

Thanks

Add this function to a code module.

Public Function Next11(StringToSearch As String,
StringToFind
As
String)
As
String

Dim retval As String
retval = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind
,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

End Function

you can now use this in code or in a query
Example
temp = Next11("abcdefghijklmnopqrstuv", "bcd")
temp = Next11(me.SomeTextbox, "bcd")
in a query
SomeColumnName: Next11(SomeFieldName, "bcd")




Is there a way of searching a field for a certain text
string
and
then
pulling the next 11 characters into a new field
Click to expand...
Click to expand...
during
a
Click to expand...
Click to expand...
 
Here's the function as it stands:

Public Function Next11(StringToSearch As String, StringToFind As String) As String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find

RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind, vbTextCompare) + Len(StringToFind), 11)

'Set a reference to the Regular Expression object
'You Must add a reference to Microsoft VBScript Regular Expressions 5.5

Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits

Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier you could just use retval = nums

RetVal = Left(Nums, 11)

Next11 = RetVal <-------- errors here

End Function


Hope this helps.

thanks
 
HSalim,

Tried your new code still get an error but puts a £ sign in the field.

cheers

HSalim said:
I just tried it and did not get a type mismstch. I did get an overflow
error though.
Since you are interested in a max of 11 numbers, you can use the currency
data type or a double


Anyway, here is the function again.

next11("dkdkb0001234512345rkr","kb") returns 12345123

HS

----------------------------
Public Function Next11(StringToSearch As String, StringToFind As String) As
Currency

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find
'retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular Expressions 5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier
RetVal = (Left(Nums, 11))


Next11 = CCur(RetVal)

End Function
-----------------------------






Brendan Reynolds (MVP) said:
It's getting a bit confusing reading back through the previous posts and
trying to picture what the code looks like at this stage. Could you post the
code as it is now, and indicate on which line it breaks?
Click to expand...
StringToFind
As
String)
As Long
This will automatically strip the leading zeros

good luck




I need a slight alteration to the code. Once it has got the string
of
numbers, I now need it to remove all preceeding 0's (zero's), how
can
this
be done??

thanks



Sorry being a bit thick. I'm new to Access, VB etc, how do
Click to expand...
you
do
this??

If you had read the code you would have seen
' You Must add a reference to Microsoft VBScript Regular
Expressions
5.5




I tried to put this in but it comes up with the following
error
'Compile
error user defined type not defined' on the line 'Dim
Click to expand...
regEx
As
New
RegExp'.
Any ideas?


Here is the answer you are looking for:

Public Function Next11(StringToSearch As String,
StringToFind
As
String)
As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to
find
'retval = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind), 11)
'this line will extract all chars after the string to find
RetVal = Mid(StringToSearch, InStr(1, StringToSearch,
StringToFind,
vbTextCompare) + Len(StringToFind))

'Set a reference to the Regular Expression object
' You Must add a reference to Microsoft VBScript Regular
Expressions
5.5
Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only
the
first
match

'Replace all non-digits
Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been
cut
down
earlier
you could just use retval = nums
RetVal = Left(Nums, 11)

Next11 = RetVal

End Function

-----------------------------------------------------------
BDCC,
Yes, you may ask for more assistance, but please post your
questions
to
the
newsgroup - others might benefit from that as well.
And please do not request read receipts on mails to me.
HS

-----Original Message-----
From: BDCC [mailto:[email protected]]
Sent: Wednesday, August 13, 2003 11:31 AM
To: (e-mail address removed)
Subject: Re: help required


Great this is exactly what I was looking for, but can
Click to expand...
Click to expand...
I
be fields
etc. during
Click to expand...
Click to expand...
 
I get a compile error on the following line ...

Nums = regEx.Replace(RetVal, replval)

There is no variable named 'replval' declared in this procedure. Not being
familiar with RegEx, I'm not sure what was intended here, but now that we've
identified the source of the problem, perhaps the person who originally
suggested this solution can help.

--
Brendan Reynolds (Access MVP)
(e-mail address removed)

Here's the function as it stands:

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Dim RetVal As String, Nums As String

'This line will extract the next 11 chars after string to find

RetVal = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind,
vbTextCompare) + Len(StringToFind), 11)

'Set a reference to the Regular Expression object
'You Must add a reference to Microsoft VBScript Regular Expressions 5.5

Dim regEx As New RegExp
regEx.Pattern = "\D" 'Set the pattern as all non digits
regEx.Global = True 'Find all matches. False finds only the first match

'Replace all non-digits

Nums = regEx.Replace(RetVal, replval)

' Get the next 11 numbers - Not required if retval has been cut down earlier
you could just use retval = nums

RetVal = Left(Nums, 11)

Next11 = RetVal <-------- errors here

End Function


Hope this helps.

thanks
 
That problem is easily fixed - I just forgot to explicitly declare the
variable replval
and you had Option Explicit - I did not.
So add these lines to your code
Dim ReplVal as string
Replval = ""
Alternatively, you could use
Const ReplVal = vbNullString

Still, I do not understand the type mismatch error - I do not get it.
perhaps the input string did not have any numeric values to extract?

Regards
HS
 
I haven't tested this, but my guess would be that because ReplVal wasn't
declared, and was therefore defaulting to a Variant, it was evaluating as
Null (or possibly Empty) instead of a zero length string. Hence the type
mismatch - VBA was encountering a Variant where it was expecting a String.
 
no, because it would have caused the same error in my system.
BDCC was getting an error on the next line -
Next11 = RetVal
which means that VB was not able to implicitly convert the value of RetVal
to a string.

HS
 
You're right. If I turn off Option Explicit, the code runs without error on
my system too, and appears to return the correct result (assuming that I
understand correctly what the desired result is).

? next11("0-123456789012345","-")
12345678901
 
If I *have* correctly understood the desired result, then how about this
alternative solution, which doesn't require any external reference?

Public Function Next11(StringToSearch As String, StringToFind As String) As
String

Next11 = Mid$(StringToSearch, InStr(1, StringToSearch, StringToFind) +
1, 11)

End Function

Note that this code doesn't test for any unexpected conditions, e.g. the
field contains a Null value, or contains a string that doesn't include the
character specified in the StringToFind argument, or is less than 11
characters long. In a finished app, we'd need to either modify the code to
test for those conditions, or check for those conditions before calling this
function.
 
All of this is just an academic exercise because I think BDCC has probably
fixed his problem.
Anyway, the original requirement was this
Is there a way of searching a field for a certain text string and then
pulling the next 11 characters into a new field during a query?
Click to expand...

My response was:
retval = Mid(StringToSearch, InStr(1, StringToSearch, StringToFind ,
vbTextCompare) + Len(StringToFind ), 11)
Next11 = retval

You will see that it is not too different to your suggeston.
(I provided for situations where Len(StringToFind ) >1)

The requirement then changed to:
Great this is exactly what I was looking for, but can I be cheeky and ask
you to refine the function slightly. The field I'm searching through
contains all manner of characters including line fields etc. In the eleven
character string it finds can it filter out all characters except numbers?
Click to expand...

Now I could have avoided making a reference to the Regular Expressions
object and iterating through the
next11 chars, and removing non-numeric chars.
Iterating through 11 chars may not privide a significant benefit over
RegExp, but
What if the spec changed to finding next 11 numeric chars randomly
interspersed in a 100 char string?
Example: "djdjf234v gfr4567 asfaa dfgs 4 rrh9 deete4 ..."
What if the spec changed to find the various numberic groups?
You could use the matches and submatches collection to cherry-pick through
the string.
I chose regexp because
a. it is elegant and seemed, to me, to be the most appropriate tool.
b. I'm guessing that the regexp object would be faster because it uses low
level functions internally to do the same iteration. I havent tested it to
confirm my guess.

A side benefit was that it was a good way to introduce the concept of RegExp
and hopefully others may discover this oft-forgotten gem.

So that was what I was thinking.

HS
 
Thanks. I missed that additional requirement, and that's a good point about
providing for situations where StringToFind may be longer than one
character.

I tend to avoid external references as much as possible, but that's because
I develop apps that will be deployed outside my own company. If the original
poster is developing an app for his own use, or for the use of others with
whom he works closely, then it becomes much less of a problem to ensure that
all the PCs have the same version of the referenced type library.
 
Back
Top