Help with Order By for concatenated dates

[eerich]

Hi everyone,

I'm having trouble with an order by statement where my date is in text format. I've attempted the following but it wouldn't produce the correct results:

Select user, mid(startdt, 3,2) & '/' & right(startdt,2) & '/' & left(startdt, 2) as Start
from Employee
order by cdate(mid(startdt, 3,2) & '/' & right(startdt,2) & '/' & left(startdt, 2)) desc, user;

Data i have is:
Marc, 060423
Bryan, 100515
Anita, 090127
Kevin, 090424
Gloria, 100512

Results expected are:
Bryan 05/15/10
Gloria 05/12/10
Kevin 04/24/09
etc

Results I currently get are:

Gloria 05/12/10
Bryan 05/15/10
Marc 04/23/06
Kevin 04/24/09

Any help is appreciated.

 

[MajP]

SELECT 
 User, 
 stDate, 
 getDateFromStr([stDate]) AS dtmDate
FROM 
 Employee
ORDER BY 
 getDateFromStr([strDate]);

in a standard module

Public Function getDateFromStr(strDate As Variant) As Date
  Dim yr As Integer
  Dim mnth As Integer
  Dim dy As Integer
  If Not IsNull(strDate) Then
     yr = CInt("20" & Left(strDate, 2))
     mnth = CInt(Mid(strDate, 3, 2))
    dy = CInt(Right(strDate, 2))
    getDateFromStr = DateSerial(yr, mnth, dy)
  End If
End Function

I am not a big fan of doing a lot of vb in the query. It limits the reusability. It is just cleaner to build a custom function.

 

[kcmass]

I think the OP is working with and looking for output in Month/Day/Year format so tweak code as:


Public Function getDateFromStr(strDate As Variant) As Date
Dim yr As Integer
Dim mnth As Integer
Dim dy As Integer
Dim DateDate As Date
If Not IsNull(strDate) Then
yr = CInt(Right(strDate, 2))
mnth = CInt(Left(strDate, 2))
dy = CInt(Mid(strDate, 3, 2))
getDateFromStr = DateSerial(yr, mnth, dy)
End If
End Function

Regards

Kevin

 

[eerich]

Thanks for the suggestions above. Is there any way to achieve this using just SQL? I'd like to avoid the module if possible.

Thanks

 

[dhookom]

If you don't want to use code, try:

Select User, CDate(Mid(startdt,3,2) & "/" & Right(startdt,2) & "/" & Left(startdt,2)) as StartDate
FROM Employee
ORDER BY CDate(Mid(startdt,3,2) & "/" & Right(startdt,2) & "/" & Left(startdt,2)) DESC;

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

No there is no way of doing this in pure sql. Duane's suggestion is just individual vb funtions in the Sql code vice encapsulating this in a single function. I can give you several reasons why I believe my approach is better. Mainly it handles a null value, and you can apply additional error checking. The other approach will lock up with any null values.

Kevin,
My code is correct. Example
090424
Clearly the year is left 2, the month is mid 3,2 and the the day is right 2. The result is a date and can be formatted in any manner desired.

 

[dhookom]

I would also implement MajP's solution. It isn't just available in a query. It can be used in code or control sources, etc.

You can write similar functions in MS SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, why not simply replace this:
order by cdate(mid(startdt, 3,2) & '/' & right(startdt,2) & '/' & left(startdt, 2)) desc, user;
with this ?
order by startdt desc, user;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

I receive a 'Datatype Mismatch Error' when using the CDate function.

SELECT   UserID   ,
         UserName ,
         cdate(IIf((LEN(pswdlastchangeddate)=5),Mid(pswdlastchangeddate,2,2) & "/" & RIGHT(pswdlastchangeddate,2) & "/0" & LEFT(pswdlastchangeddate,1),Mid(pswdlastchangeddate,3,2) & "/" & RIGHT(pswdlastchangeddate,2) & "/" & LEFT(pswdlastchangeddate,2))) AS PswdChangedOn
FROM     Employees
ORDER BY Cdate(PswdChangedOn) DESC,
         UserID;

Any additional help is appreciated.

 

[PHV]

What are pswdlastchangeddate and PswdChangedOn ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Not to be mean, but I hate to say I told you so...

You do not want to encapsulate your code in a UDF because:
1)You do not want to error check the values passed in?
2)You like to write hard to read iif functions in your sql
3)You only want to be able to use this once?
4)You like writing long sql code with lots of vb functions embedded within?
5)You do not like the option of debugging the function outside of a query?

 

[MajP]

Public Function getPwordDate(strDate As Variant) As Date
  Dim yr As Integer
  Dim mnth As Integer
  Dim dy As Integer
  If Not IsNull(strDate) Then
     If Len(strDate) = 5 Then
     'ymmdd input
      strDate = "0" & strDate
     End If
     If Len(strDate) = 6 Then
       yr = CInt("20" & Left(strDate, 2))
       mnth = CInt(Mid(strDate, 3, 2))
       dy = CInt(Right(strDate, 2))
       getPwordDate = DateSerial(yr, mnth, dy)
    End If
  End If
End Function
[code]
Tested in the immediate window. Handles 6 string, 5 string, null, and other than 6.
?getPwordDate("91012")
10/12/2009 
?getPwordDate("101012")
10/12/2010 
?getPwordDate(null)
0 
?getPwordDate("123")
0

 

[eerich]

I appreciate your help, MajP. I'm not well versed in VB and thus I try to avoid the module bc vie had many issues given I can't debug very well as you've indicated. I will give it a try and see if I can get it to work.

PHV - PswdlastchangedDate is a text field. The Pswdlastchangedon is an alias of the field for labeling purposes.

Thanks all for your assistance.

 

[PHV]

So, change this:
ORDER BY Cdate(PswdChangedOn) DESC,
with this:
ORDER BY Val(pswdlastchangeddate) DESC,

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

My point is that that this:

"cdate(IIf((LEN(pswdlastchangeddate)=5),Mid(pswdlastchangeddate,2,2) & "/" & RIGHT(pswdlastchangeddate,2) & "/0" & LEFT(pswdlastchangeddate,1),Mid(pswdlastchangeddate,3,2) & "/" & RIGHT(pswdlastchangeddate,2) & "/" & LEFT(pswdlastchangeddate,2)))"

is 8 vb functions, and pretty complicated with an iif wrapping the functions. It is very difficult to debug this. With a little practice a user function is much less complicated to write and far easier to error check and debug. And the sql just gets a lot simpler

SELECT
  UserID   ,
  UserName ,
  getPwordDate([pswdlastchangeddate]) AS PswdChangedOn
FROM     
  Employees
ORDER BY 
  getPwordDate([pswdlastchangeddate]) DESC,
         UserID

 

[eerich]

Hi PHV,

That worked perfectly!

Thanks

 

[PHV]

eerich, FYI, you already had the answer 9 Aug 10 3:48