Is there a function that will strip alpha prefixes from number in an Access query?

I have a text field (JobNumber) that is an alpa prefix combined with a number.  Currently it is always a one digit alpa prefix.

A12345
C54321
etc.

Several queries use Mid(JobNumber,2) and Left(JobNumber,1) to get just the prefix or the number without the prefix.

We now have the capability of having a 2 digit alpha prefix but all existing records will retain the 1 digit alpha prefix

A12345
C54321
BR98765
KT56789

Are there functions that I an use to replace the MID and LEFT to return ONLY the alpha prefixes (1 or 2 digits) and ONLY the number part of the JobNumber?

Thanks!
etc.
lthamesAsked:
Who is Participating?
 
CompanionCubeConnect With a Mentor Commented:
http://www.ozgrid.com/forum/showthread.php?t=45743
possible to remove the numbers from the end of string variable using a simple VB function?


Function StripNumber(stdText As String)
    Dim str As String, i As Integer
     'strips the number from a longer text string
    stdText = Trim(stdText)
     
    For i = 1 To Len(stdText)
        If Not IsNumeric(Mid(stdText, i, 1)) Then
            str = str & Mid(stdText, i, 1)
        End If
    Next i
     
    StripNumber = str ' * 1
     
End Function
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Will these Job Numbers be either 6 (old version) or 7 (new version) characters ?

mx
0
 
lthamesAuthor Commented:
No.
The alpha portion will always either be 1 or 2 letters.
The 'number' portion can be anywhere between 1 digit to 5 digits.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
CompanionCubeCommented:
http://www.techonthenet.com/access/functions/numeric/val.php

In Access, the Val function accepts a string as input and returns the numbers found in that string.

The syntax for the Val function is:

Val ( string )

string is a string expression.
0
 
lthamesAuthor Commented:
what about returning only the alpha characters at the beginning?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Val() ... well, not quite.

val("BR98765")  returns 0

However, val("98765AAA")  returns 98765

mx
0
 
lthamesAuthor Commented:
I can write a vb function . . . . I'm actually a VB programmer

But this database has several queries that are very complicated and are for a client . . .so I must leave the functionality as it . . . . except for modifying the actual query.  

Now, if I can make the query call the function that would work . . .is that possible?
0
 
lthamesAuthor Commented:
But I would rather accomplish this with standard access functions if this is possible.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"accomplish this with standard access functions if this is possible."

Code will be necessary.

mx
0
 
hnasrCommented:
try this as expression in your query:

Left(JobNumber,Len(JobNumber)-Len(cStr(Val(StrReverse(JobNumber)))))
0
 
lthamesAuthor Commented:
hnasr,

That is really clever!  It works . . . unless the last digit of the actual JobNumber is a zero.

Z5110 returns Z5 instead of just Z

I've given up the standard function route and wrote a vba function.  Now I'm just hoping my client will accept that :)

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"unless the last digit of the actual JobNumber is a zero."
One of the drawbacks of Val()

Take a look at this discussion if you are going to use Val() ... and note the pitfalls ... especially a was down ... where Gustav chimes in ...

mx
0
 
hnasrCommented:
Left(JobNumber,Len(JobNumber)-Len(cStr(Val("9" & StrReverse(JobNumber))))+1)
0
 
CompanionCubeCommented:
I admit I'm adding to the previous post in this question, but this should give you want you want no matter if there's a leading or trailing '0' (zero)...

This was tested in VB6:

Gives you the 'alpa prefix' of your JobNumber...(no matter how many letters in the prefix)
Text2.Text = Left(JobNumber, Len(JobNumber) - Len(CStr(Val("9" & StrReverse(JobNumber)))) + 1)

Gives you the 'number part' of your JobNumber as a string... (no matter if you have trailing zeros or not! The entire JobNumber can be all zeros! i.e. AB000000 would return '000000')
Text3.Text = Mid(StrReverse(Val("9" & StrReverse(JobNumber)) & "9"), 2, (Len(JobNumber) - Len(Left(JobNumber, Len(JobNumber) - Len(CStr(Val("9" & StrReverse(JobNumber)))) + 1))))
0
 
CompanionCubeCommented:
The only rules are... for the above to work, your JobNumber must be in the format you specified.
"I have a text field (JobNumber) that is an alpa prefix combined with a number."

Yes the above will work with any number of alpha prefix letters
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.