lvjan777
asked on
Microsoft, Access, 2007, Sort text field containing dates and text
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not really a good idea to mix and match ...definitely going to complicate things.
What does the data look like?
mx
What does the data look like?
mx
That is a horrible idea; protect yourself and the users from themselves by offering alternatives.
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.
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]
But see this as a quick and dirty get round until you have separate date and comments fields.
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
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
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.
ASKER
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
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
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(DueD ate, "\d{1,2} [a-z]{3} \d{2,4}", 1, False)), Format(CDate(RegExpFind(Du eDate, "\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...
2) Run a query like this:
SELECT Title, DueDate
FROM SomeTable
ORDER BY IsDate(RegExpFind(DueDate,
IIf(IsDate(RegExpFind(DueD
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
ASKER