I need help with how I can do this. I have a field in a database that represents a Classification code from say 10 to 100, the number being the code, problem being is that some codes can be 15MMA for example. I need to be able to pull 25-75 wether there is letters in the code or not but I cant use <> since its "text".
Anyone out there have any suggestions? I am under the gun with this report
I already have something that would do the job for you.
What you do is to enter a letter such F and then click and remove every F on every record. You then enter another letter and it will do the same.
I made it for formatting phone numbers so that I could remove spaces () -_ and so on but I also set it up so if necessary I enter anything. In other words where you enter the F or A etc you can put in an * or % or whatever and it will know them out.
It is on Access 95. Although it is based on a table that has about 240 fields it only uses about 4 of them so you add those field names to your table and then make a copy of your table and so its name was the same.
So say you had 1000 records and the field in question had entries like abd345, ert567 etc then you would enter a letter from the alphabet and it would run down the recordset and remove all them. Then you would do the next letter.
It changes the actual data in the field because the results of the calculated fields are fed back to the field by a macro SetValue action. So it feeds off itself.
The position of the letters does not matter. For example if has records with aaa234aaa123 it will leave you with 234123 when you do the a. Also if you have spaces (this might be a negative for your setup) it will knock them out at the same time. So aswed345 34 567fsfsa will become 34534567
If you are interested I could attach the thing for you to use.
I would create a Classification Code table with three fields, the Code, the numeric part of the code and the alpha part of the code. (Using a make query with grouping to make a table would be the best way to go...)
Then I'd run code the following code on the table:
Public Sub ParseCodeNumber()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCode As String
Dim strAlphaPart As String
Set db = CurrentDb
Set rs = db.OpenRecordset("ClassCodeTableName")
While Not rs.EOF
strCode = rs![ClassCodeField]
strAlphaPart = ""
While Not IsNumeric(Right(strCode, 1)) And Len(strCode) <> 0
strAlphaPart = strAlphaPart & Right(strCode, 1)
strCode = Left(strCode, Len(strCode) - 1)
If Len(strCode) = 0 Then ' if the Code is completely Alpha
strCode = "0"
If strAlphaPart = "" Then ' if the Code is completely numeric
strAlphaPart = "NONE"
rs![NumericPartField] = Val(strCode)
rs![AlphaPartField] = strAlphaPart
Set rs = Nothing
Set db = Nothing
Then link your main table to the new table and sort by the numeric field.