• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

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.
0
lthames
Asked:
lthames
  • 5
  • 4
  • 4
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
lthamesAuthor Commented:
what about returning only the alpha characters at the beginning?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Val() ... well, not quite.

val("BR98765")  returns 0

However, val("98765AAA")  returns 98765

mx
0
 
CompanionCubeCommented:
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
 
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
"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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now