How to Query for the most recent payment by date in the format YYMMDD

[pbaldy]

One way is here:

http://allenbrowne.com/subquery-01.html#AnotherRecord

You can't use -1 because your data isn't really stored as a date. IOW, 1501 - 1 is 1500, not 1412.

There are a number of us who do Access work, and not just moderators. You can start a new thread looking for candidates (a new thread will get more attention). I do some work on the side, but I have a full time job as well, so I only take on stuff I can fit into my schedule.

[Russellh]

I'll start that tab. THe Bill MOYR is a # that you can subtract from. We us 1509 for 2015 september . August will be 1508 so -1 works and should work even for regular dates as they use the format YYMMDD which can always be added to and subtracted from.

[pbaldy]

I don't see it that way. Subtract 1 from 0701; do you get 0612? It works during the year, but not across years. Even the YYMMDD doesn't work. Subtract 1 from 060601; you don't get 060531. If you want to do date comparisons/math, the field should be date/time. That said, the subquery in Allen's example should work, because it uses < rather than -1.

[Russellh]

Agreed the way he did it with TOP but < should work. I just couldn't make it do it. And this is one of those where you cant find out 6 months from now something was inaccurate. So Im gonna get a pro to make it all work!! Thanks for all the help though!

[June7]

Is this the same issue in your other thread https://www.accessforums.net/access/...put-55353.html

[Russellh]

Yes as since it was a 2nd part question and pbaldy didnt respond for a while I assumed he closed it out and I created a new ticket to address the new problem.

[June7]

Well, you haven't addressed my last post in that thread.

[InsuranceGuy]

You probably want to convert your dates to variant-date values before you start trying to manipulate them. This will allow you to use functions like DateAdd to compare sequential months.

Here are series of functions I had stashed away for this purpose.

Option Compare Database


'Functions included:
'Y4MDToDate - Takes string input from YYYYMM or YYYYMMDD format and outputs a variant date
'DateToY4MD - Takes variant date in and outputs YYYYMMDD format
'DateToY4M  - Same as DateToY4MD but drops calendar day, outputs YYYYMM format
'Y2MDToDate - Takes string input from YYMM or YYMMDD format and outputs a variant date
'DateToY2MD - Takes variant date in and outputs YYMMDD format
'DateToY2M  - Same as DateToY2MD but drops calendar day, outputs YYMM format




Public Function Y4MDToDate(varDate As Variant) As Variant


    On Error GoTo errHandle
    
    If Len(varDate) = 8 Then
        'YYYYMMDD format
        Y4MDToDate = DateSerial(CInt(Left(varDate, 4)), CInt(Mid(varDate, 5, 2)), CInt(Right(varDate, 2)))
    ElseIf Len(varDate) = 6 Then
        'YYYYMM format
        Y4MDToDate = DateSerial(CInt(Left(varDate, 4)), CInt(Mid(varDate, 5, 2)), 1)
    Else
        'Incorrect format
        Y4MDToDate = Null
    End If
    
    Exit Function


errHandle:
    Y4MDToDate = Null
End Function


Public Function DateToY4MD(varDate As Variant) As Variant
    
    On Error GoTo errHandle


    DateToY4MD = Format(varDate, "YYYYMMDD")
    Exit Function


errHandle:
    DateToY4MD = Null
    
End Function


Public Function DateToY4M(varDate As Variant) As Variant


    On Error GoTo errHandle
    
    DateToY4M = Format(varDate, "YYYYMM")
    Exit Function
    
errHandle:
    DateToY4M = Null


End Function




Public Function Y2MDToDate(varDate As Variant) As Variant


    On Error GoTo errHandle
    
    If Len(varDate) = 6 Then
        'YYMMDD format
        Y2MDToDate = DateSerial(CInt(Left(varDate, 2)), CInt(Mid(varDate, 3, 2)), CInt(Right(varDate, 2)))
    ElseIf Len(varDate) = 4 Then
        'YYMM format
        Y2MDToDate = DateSerial(CInt(Left(varDate, 2)), CInt(Mid(varDate, 3, 2)), 1)
    Else
        'Incorrect format
        Y2MDToDate = Null
    End If
    
    Exit Function


errHandle:
    Y2MDToDate = Null
End Function


Public Function DateToY2MD(varDate As Variant) As Variant
    
    On Error GoTo errHandle


    DateToY2MD = Format(varDate, "YYMMDD")
    Exit Function


errHandle:
    DateToY2MD = Null
    
End Function


Public Function DateToY2M(varDate As Variant) As Variant


    On Error GoTo errHandle
    
    DateToY2M = Format(varDate, "YYMM")
    Exit Function
    
errHandle:
    DateToY2M = Null


End Function

Good luck,


Jeff