Results 1 to 4 of 4

Thread: Query problem

  1. #1
    Join Date
    Apr 2004
    Location
    Newbury, Berkshire, UK
    Posts
    2

    Unanswered: Query problem

    I have a text field (named RouteCode) in an Access table in the following range of formats:

    ABCD/1
    ABCD/1A
    ABCD/12
    ABCD/12A
    ABCD/123
    ABCD/123A

    I have a query that feeds to a report, and I would like the report to be sorted numerically on the basis of the 123 and then the A (where it is present)

    I am a near beginner to all this, but it seems to me that what I need to do is somehow extract the 123 in a new query column and then convert it to a number, and in another query column to extract the final A (where it is present).

    The 'A' is actually restricted to the letters A,B,C,D,P or X

    Does this make sense, and if so is it possible or am I barking up the wrong tree?

    Any suggestions would be gratefully received

    nick

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    GAH THAT'S UGLY!!

    Why do you have to do this? Are you importing someone elses info? If not, you should pull this stuff into a better table structure.

    Assuming you cant:

    RIGHT(RouteCode, LEN(RouteCode) - InStr(RouteCode ,"/", 0))

  3. #3
    Join Date
    Apr 2004
    Location
    Newbury, Berkshire, UK
    Posts
    2
    You guessed...it's an off-the-shelf database that doesn't quite meet my needs!

    It's my data...but not quite how I want it, and although I could change the table structure to suit, I am unable to change the associated forms to allow all the various users to input the data as wanted.

    I'll give your suggestion a try on Monday when I'm back at work..nick

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I posted this parser yesterday to another thread.

    It parses part numbers based upon a change from letters to numbers.

    Code:
    Function ParseTest() As Boolean
    
        Dim rs As Recordset
        Dim LastCharacterIsNumber As Boolean
        Dim Segment As Byte
        Dim SegmentStartPosition As Byte
        Dim i As Byte
        
        Set rs = CurrentDb().OpenRecordset("Table1")
        
        Do Until rs.EOF
            rs.Edit
            Segment = 1
            SegmentStartPosition = 1
            LastCharacterIsNumber = IsNumeric(Left$(rs("PartNumber"), 1))
        
            For i = 2 To Len(rs("PartNumber"))
                If IsNumeric(Mid$(rs("PartNumber"), i, 1)) <> LastCharacterIsNumber Then
                    LastCharacterIsNumber = Abs(LastCharacterIsNumber) - 1
                    rs("Segment" & Segment) = Mid$(rs("PartNumber"), SegmentStartPosition, i - SegmentStartPosition)
                    Segment = Segment + 1
                    SegmentStartPosition = i
                End If
            Next i
            LastCharacterIsNumber = Abs(LastCharacterIsNumber) - 1
            rs("Segment" & Segment) = Mid$(rs("PartNumber"), SegmentStartPosition, i - SegmentStartPosition)
            rs.Update
            rs.MoveNext
        Loop
        
        rs.Close
        Set rs = Nothing
    
    End Function

Posting Permissions

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