truncate characters

S

Susie

I have a field with data that has 3 alpha and 4 numeric
characters (ex. AAA-0000) I need to create a new field
with just the numeric characters (however, the last four
are not always numeric, it could be (ex. AAA_A000)). Is
there a way to do this in Access? Thank you very much for
your help!!
 
B

Brendan Reynolds

If the number of digits is not fixed, and there is no fixed separator
between the letters and the numbers, then I don't see any way to do it other
than walking the string and testing each character. If the digits *always*
come at the end of the string, with no other characters between or after the
digits, we can save a few processor cycles by starting at the end of the
string and exiting as soon as we find the first non-digit:

Public Function GetDigitsFromEnd(ByVal strInput As String) As String

Const cstrDigits As String = "0123456789"

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

For lngLoop = Len(strInput) To 1 Step -1
strChar = Mid$(strInput, lngLoop, 1)
If InStr(1, cstrDigits, strChar) <> 0 Then
strWork = strChar & strWork
Else
Exit For
End If
Next lngLoop

GetDigitsFromEnd = strWork

End Function

Here's an example of how we could call this function in a query:

SELECT tblTest.TestText, GetDigitsFromEnd([TestText]) AS Digits
FROM tblTest;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me
to use a real e-mail address in public newsgroups. E-mail replies to this
post
will be deleted without being read. Any e-mail claiming to be from brenreyn
at
indigo dot ie that is not digitally signed by me with a GlobalSign digital
certificate is
a forgery and should be deleted without being read. Follow-up questions
should
in general be posted to the newsgroup, but if you have a good reason to send
me e-mail, you'll find a useable e-mail address at the URL above.
 
G

Guest

I know there is a function (Right, I think) in Excel that
will allow you to pull a fixed number of characters from a
field. Is there such a thing in Access?
-----Original Message-----
If the number of digits is not fixed, and there is no fixed separator
between the letters and the numbers, then I don't see any way to do it other
than walking the string and testing each character. If the digits *always*
come at the end of the string, with no other characters between or after the
digits, we can save a few processor cycles by starting at the end of the
string and exiting as soon as we find the first non-digit:

Public Function GetDigitsFromEnd(ByVal strInput As String) As String

Const cstrDigits As String = "0123456789"

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

For lngLoop = Len(strInput) To 1 Step -1
strChar = Mid$(strInput, lngLoop, 1)
If InStr(1, cstrDigits, strChar) <> 0 Then
strWork = strChar & strWork
Else
Exit For
End If
Next lngLoop

GetDigitsFromEnd = strWork

End Function

Here's an example of how we could call this function in a query:

SELECT tblTest.TestText, GetDigitsFromEnd([TestText]) AS Digits
FROM tblTest;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me
to use a real e-mail address in public newsgroups. E-mail replies to this
post
will be deleted without being read. Any e-mail claiming to be from brenreyn
at
indigo dot ie that is not digitally signed by me with a GlobalSign digital
certificate is
a forgery and should be deleted without being read. Follow-up questions
should
in general be posted to the newsgroup, but if you have a good reason to send
me e-mail, you'll find a useable e-mail address at the URL above.

I have a field with data that has 3 alpha and 4 numeric
characters (ex. AAA-0000) I need to create a new field
with just the numeric characters (however, the last four
are not always numeric, it could be (ex. AAA_A000)). Is
there a way to do this in Access? Thank you very much for
your help!!
Click to expand...


.
Click to expand...
 
B

Brendan Reynolds

There is, and it has the same name, but I'm not at all sure from reading the
original post that the number of characters is fixed ...

<quote>
I have a field with data that has 3 alpha and 4 numeric characters (ex.
AAA-0000). I need to create a new field with just the numeric characters.
</quote>

OK, if this is always the case, all we need is the Right() function, but ...

<quote>
(however, the last four are not always numeric, it could be (ex. AAA_A000)).
</quote>

So, if what is wanted is simply the right-most 4 characters, yes, the
Right() function is all that is needed, but if what is wanted is the digits,
however many there may be, then we need something more like my example.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


I know there is a function (Right, I think) in Excel that
will allow you to pull a fixed number of characters from a
field. Is there such a thing in Access?
-----Original Message-----
If the number of digits is not fixed, and there is no fixed separator
between the letters and the numbers, then I don't see any way to do it other
than walking the string and testing each character. If the digits *always*
come at the end of the string, with no other characters between or after the
digits, we can save a few processor cycles by starting at the end of the
string and exiting as soon as we find the first non-digit:

Public Function GetDigitsFromEnd(ByVal strInput As String) As String

Const cstrDigits As String = "0123456789"

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

For lngLoop = Len(strInput) To 1 Step -1
strChar = Mid$(strInput, lngLoop, 1)
If InStr(1, cstrDigits, strChar) <> 0 Then
strWork = strChar & strWork
Else
Exit For
End If
Next lngLoop

GetDigitsFromEnd = strWork

End Function

Here's an example of how we could call this function in a query:

SELECT tblTest.TestText, GetDigitsFromEnd([TestText]) AS Digits
FROM tblTest;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me
to use a real e-mail address in public newsgroups. E-mail replies to this
post
will be deleted without being read. Any e-mail claiming to be from brenreyn
at
indigo dot ie that is not digitally signed by me with a GlobalSign digital
certificate is
a forgery and should be deleted without being read. Follow-up questions
should
in general be posted to the newsgroup, but if you have a good reason to send
me e-mail, you'll find a useable e-mail address at the URL above.

I have a field with data that has 3 alpha and 4 numeric
characters (ex. AAA-0000) I need to create a new field
with just the numeric characters (however, the last four
are not always numeric, it could be (ex. AAA_A000)). Is
there a way to do this in Access? Thank you very much for
your help!!
Click to expand...


.
Click to expand...
Click to expand...
 
Top