Extracting a reference from a field

[Guest]

Hi,

I've got a table which has an order reference field. I need to extract a 5
character code from this field. Unfortunately, due to some being
automatically generated by different systems and others being manually
entered there is no consistency to the format, therefore I can't use mid,
left functions to get the info. However, in every case the code that I need
always begins with E and is then followed by 4 numbers - is there a formula
that I could use that could find and extract this code?

Any help greatly appreciated.

 

[BruceM]

Assuming the field in question is named OrderRef, in a query field you could
put something like:
ShortCode: Mid([OrderRef],InStr([OrderRef],"E"),5)
You could do the same thing in an unbound text box by replacing "ShortCode:
" with an = sign, or in VBA, but it sounds as if a query may be the cleanest
option.
The expression assumes you always have E0000 in the field, and that there is
either only one letter E or that the first letter E is the one you need.

 

[BruceM]

Assuming the field in question is named OrderRef, in a query field you could
put something like:
ShortCode: Mid([OrderRef],InStr([OrderRef],"E"),5)
You could do the same thing in an unbound text box by replacing "ShortCode:
" with an = sign, or in VBA, but it sounds as if a query may be the cleanest
option.
The expression assumes you always have E0000 in the field, and that there is
either only one letter E or that the first letter E is the one you need.

 

[Guest]

Here is some code that should help:

Dim varCharLoc
Dim strSearchText As String
Dim strCode As String

strSearchText = Me.txtInput
varCharLoc = InStr(1, strSearchText, "E")
'if the letter "E" is in the string
If varCharLoc > 0 Then
'read the code into the variable "strCode"
strCode = Mid(strSearchText, varCharLoc, 5)
Me.txtCode = strCode
End If


This code takes the entry in a text box named "txtInput" and assigns it to a
local vaiable named "strSearchText". Then the value in the variable is
checked to see if the "E" is actually in the string.
If it is there, then it will extract the "E" plus the next 4 characters
assigning the value extracted to a local variable named "strCode". The value
in the "strCode" variable is then placed in another text box named "txtCode".

You can adjust these parameters as needed.

 

[Guest]

Here is some code that should help:

Dim varCharLoc
Dim strSearchText As String
Dim strCode As String

strSearchText = Me.txtInput
varCharLoc = InStr(1, strSearchText, "E")
'if the letter "E" is in the string
If varCharLoc > 0 Then
'read the code into the variable "strCode"
strCode = Mid(strSearchText, varCharLoc, 5)
Me.txtCode = strCode
End If


This code takes the entry in a text box named "txtInput" and assigns it to a
local vaiable named "strSearchText". Then the value in the variable is
checked to see if the "E" is actually in the string.
If it is there, then it will extract the "E" plus the next 4 characters
assigning the value extracted to a local variable named "strCode". The value
in the "strCode" variable is then placed in another text box named "txtCode".

You can adjust these parameters as needed.

 

[Guest]

<DIV>&quot;Statto1971&quot; &lt;[email protected]&gt;
schrieb im Newsbeitrag