truncate characters

  • Thread starter Thread starter Susie
  • Start date Start date
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!!
 
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 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...
 
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...
 
Back
Top