Solved

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

Posted on 2009-12-20
583 Views
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.
0
Question by:lthames

LVL 75

Expert Comment

Will these Job Numbers be either 6 (old version) or 7 (new version) characters ?

mx
0

Author Comment

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

LVL 7

Expert Comment

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

Author Comment

what about returning only the alpha characters at the beginning?
0

LVL 75

Expert Comment

Val() ... well, not quite.

val("BR98765")  returns 0

However, val("98765AAA")  returns 98765

mx
0

LVL 7

Accepted Solution

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

Author Comment

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

Author Comment

But I would rather accomplish this with standard access functions if this is possible.
0

LVL 75

Expert Comment

"accomplish this with standard access functions if this is possible."

Code will be necessary.

mx
0

LVL 30

Expert Comment

try this as expression in your query:

Left(JobNumber,Len(JobNumber)-Len(cStr(Val(StrReverse(JobNumber)))))
0

Author Comment

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

LVL 75

Expert Comment

"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

LVL 30

Expert Comment

Left(JobNumber,Len(JobNumber)-Len(cStr(Val("9" & StrReverse(JobNumber))))+1)
0

LVL 7

Expert Comment

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

LVL 7

Expert Comment

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

#### 729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!