Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    56

    Unanswered: Number and Text Field in a querry

    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".

    Is there a way I can pull this off?

    thanks ahead of time

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Strip the text's last three digit (assuming you alsways have last three as characters) and then pull your data

  3. #3
    Join Date
    Mar 2004
    Posts
    56
    That will not work as some codes only have 2 letters and other codes can be say 258 (3 numbers)

  4. #4
    Join Date
    Mar 2004
    Posts
    56
    I also have some codes that are 230BA

    Is there some formula that will look at the string, find the first instance of an alpha character and trunc it?

  5. #5
    Join Date
    Mar 2004
    Posts
    56
    Anyone out there have any suggestions? I am under the gun with this report

  6. #6
    Join Date
    Mar 2004
    Posts
    56
    Help!!!!!!

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by roma92
    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.

    Mike
    Last edited by Mike375; 07-09-04 at 17:13.

  8. #8
    Join Date
    Mar 2004
    Posts
    56
    Yes please that anything would help

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Try this.

    You will need a couple of instructions as this is not set up for "users"

    I will be going out quite soon so if I miss you I will be back in about 3 hours or so

    As you will have a later version of Access the best thing is to create a blank data base and import this ones tables, queries etc

    Mike
    Attached Files Attached Files

  10. #10
    Join Date
    Jul 2004
    Posts
    5
    Hi

    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:
    Code:
    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")
    
    rs.MoveFirst
    
    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)
        Wend
        
        
        If Len(strCode) = 0 Then ' if the Code is completely Alpha
            strCode = "0"
        End If
        
        If strAlphaPart = "" Then ' if the Code is completely numeric
            strAlphaPart = "NONE"
        End If
        
        rs.Edit
        rs![NumericPartField] = Val(strCode)
        rs![AlphaPartField] = strAlphaPart
        rs.Update
        
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    End Sub
    Then link your main table to the new table and sort by the numeric field.

    Good luck
    D

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by roma92
    Yes please that anything would help
    What happened, what was the outcome etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •