Solved

Microsoft, Access, 2007, Sort text field containing dates and text

Posted on 2008-10-30
9
762 Views
Last Modified: 2009-01-08
I have a text field that contains dates and text.  I want to sort the field on the report by date ascending and then the text records A-Z.  How do I do it?
0
Comment
Question by:lvjan777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 125 total points
ID: 22846479
Two different fields?

If so, you can (and should) use the report Grouping and Sorting dialog to specify the Sort order.

mx
0
 

Author Comment

by:lvjan777
ID: 22846501
No they are in the same field.  It is a due date field but the users want to put in data like TBD or In Work, in some records.
0
 
LVL 75
ID: 22846523
Not really a good idea to mix and match ...definitely going to complicate things.

What does the data look like?

mx
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 18

Expert Comment

by:jmoss111
ID: 22846667
That is a horrible idea; protect yourself and the users from themselves by offering alternatives.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22846975
Definitely not recommended. That means that the field must actually be Text field, instead of a DateTime field. That means that simple sorting will depend of the date format, which probably starts with the day or the month.

In this case there would also have to be a way to tell when the date part ends and the text begins. A query like this might work. It expects the first 8 characters to be the date.

SELECT MyTable.MyDateAndText FROM tbl2
ORDER BY CDate(Left([MyDateAndText],8)), Mid([MyDateAndText],9)

But see this as a quick and dirty get round until you have separate date and comments fields.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22849340
Hello lvjan777,

As already mentioned, this is a really terrible idea.  That said, Graham has given you a good idea to start
with, but you need to post a few examples of what the entries in this column look like.

Regards,

Patrick
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22849935
And I notice that my SQL example has its own error.. I have used two different table names (My Table and tbl2). They must, of course be the same.
0
 

Author Comment

by:lvjan777
ID: 22868013
Here's a sample of the data.  Users want to see the due dates and the comments in the same field in the report.  Is there an easy way to do that?

Title - Project One
Due date - 26 Jul 09

Title - Project Two
Due date - TBD (to be determined)

Title - Project Three
Due Date  15 Aug 09

Title - Project Four
Due Date - N/A

Requested Report Results.  Order by due date then alphabetical on records with no date:
Project One  - 26 Jul 09
Project Three - 15 Aug 09
Project Four - N/A
Project Two - TBD
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22907304
1) Add the UDF to a regular VBA module

2) Run a query like this:


SELECT Title, DueDate
FROM SomeTable
ORDER BY IsDate(RegExpFind(DueDate, "\d{1,2} [a-z]{3} \d{2,4}", 1, False)),
    IIf(IsDate(RegExpFind(DueDate, "\d{1,2} [a-z]{3} \d{2,4}", 1, False)), Format(CDate(RegExpFind(DueDate, "\d{1,2} [a-z]{3} \d{2,4}", 1, False)), "yyyy-mm-dd"), DueDate)


Your client will be far, far better off splitting the due date and the comments into separate columns...
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
    
End Function

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

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

Join & Ask a Question