.OrderBy Mixture of Start and End Date

[dgreen]

I've built a Job position experiences table and I need the subform to refresh its sort order on loading...
Need help with vba to sort by End_Date first if available but if not, then by the start date field.
The below table illustrates the desired sort order and my challenge when we don't have a complete record.

I figure the code is going to be starting off like below. Any help would be appreciated.

Private Sub Form_Load()
    If IsNull(Me.End_Date) Then
        Me.OrderBy = "[End_Date] DESC, [Start_Date] DESC"
    Elseif then
        
    End If
End Sub

 

[plog]

No need to test anything, just put your .OrderBy statement as the only code in the Sub.

 

[dgreen]

Need a little more help.... if I do the above code, the most current jobs end up at the bottom of the sort. I'm trying to keep them at the top of the sort.

No need to test anything, just put your .OrderBy statement as the only code in the Sub.

 

[MajP]

I think in your query you have this as a calculated field.

Public Function MaxDate(startDate As Variant, endDate As Variant) As Date
 if not isdate(startdate) then exit function 
 If Not IsDate(endDate) Then
   MaxDate = startDate
  Else
   MaxDate = endDate
  End If
End Function

Then sort on MaxDate

 

[dgreen]

Works but I forgot about the scenario where someone is just plugging in data and forgets both dates. I do have another date field called [Created] that could be used but how to integrate it into your public formula? I'd make the next assumption that they were entered in a logical sequence from a resume and should be presented back in that order, to eventually be populated with actual dates.

 

[MajP]

Public Function MaxDate(createdDate As Variant, startDate As Variant, endDate As Variant) As Date
  If IsDate(endDate) Then
    MaxDate = endDate
  ElseIf IsDate(startDate) Then
    MaxDate = startDate
  ElseIf IsDate(createdDate) Then
    MaxDate = createdDate
  End If
End Function

 

[dgreen]

I'm playing around with this. Seems right.
Thanks.

Public Function MaxDate(createdDate As Variant, startDate As Variant, endDate As Variant) As Date
  If IsDate(endDate) Then
    MaxDate = endDate
  ElseIf IsDate(startDate) Then
    MaxDate = startDate
  ElseIf IsDate(createdDate) Then
    MaxDate = createdDate
  End If
End Function

 

[dgreen]

Having issues with the code.

1) In this test database (attached), I can't get the Public function to work. Even though it's the same code as above. Error "Undefined function maxdate in expression"

This is what's in the query

MaxDate1: maxdate([created],[start_date],[end_date])

This is the public function

Option Compare Database
Option Explicit

Public Function MaxDate(createdDate As Date, startDate As Date, endDate As Date) As Date
    If IsDate(endDate) Then
        MaxDate = endDate
    ElseIf IsDate(startDate) Then
        MaxDate = startDate
    ElseIf IsDate(createdDate) Then
        MaxDate = createdDate
    End If
End Function

2) In my other live database only when the end date is populated does the code show a date. If end date is empty, the code shows Error. I assume that this error will occur also, once we figure out why issue 1 is happening.

I have a second public function MaxDate2 that appears to be working right in my other database that tells you which date should be chosen but MaxDate1's values aren't getting populated right.

Here's a visual of the function not quite working right.

 

[Gasman]

You have Maxdate defined somewhere else?

Your project was called that in your file, and I got that error running the query. If I tried the function in the immediate window it complained about project, which tipped me off. I renamed your project and then got the query to run

 

[dgreen]

You have Maxdate defined somewhere else?

Your project was called that in your file, and I got that error running the query. If I tried the function in the immediate window it complained about project, which tipped me off. I renamed your project and then got the query to run

View attachment 81156

Open the only module and you'll see what I have.

 

[dgreen]

I don't understand what you mean when you said you renamed the project.

You have Maxdate defined somewhere else?

It appears you got the MaxDate2 to run (last column in the query). The function that runs it is in the same module as the column MaxDate1 that is erroring.

Option Compare Database
Option Explicit

Public Function MaxDate(createdDate As Date, startDate As Date, endDate As Date) As Date
    If IsDate(endDate) Then
        MaxDate = endDate
    ElseIf IsDate(startDate) Then
        MaxDate = startDate
    ElseIf IsDate(createdDate) Then
        MaxDate = createdDate
    End If
End Function

Public Function MaxDate2(createdDate As Variant, startDate As Variant, endDate As Variant) As String

    If IsDate(endDate) Then
        MaxDate2 = "[end_Date]"
    ElseIf IsDate(startDate) Then
        MaxDate2 = "[start_Date]"
    ElseIf IsDate(createdDate) Then
        MaxDate2 = "[created]"
    End If
    
End Function

 

[dgreen]

So simple, it's in your face. The answer was in the MaxDate2 code. Change the below from As Date to As Variant. And on top of it, it was in the fixed code that @MajP had provided me. For some reason the change didn't migrate with my database updates.

FROM

Public Function MaxDate(createdDate As Date, startDate As Date, endDate As Date) As Date

TO

Public Function MaxDate(createdDate As Variant, startDate As Variant, endDate As Variant) As Date
    If IsDate(endDate) Then
        MaxDate = endDate
    ElseIf IsDate(startDate) Then
        MaxDate = startDate
    ElseIf IsDate(createdDate) Then
        MaxDate = createdDate
    End If
End Function

 

[Gasman]

I don't understand what you mean when you said you renamed the project.

The file you uploaded had it's project named as Maxdate ?

It appears you got the MaxDate2 to run (last column in the query). The function that runs it is in the same module as the column MaxDate1 that is erroring.

How do you explain the value in the Maxdate1 column then?

 

[KamleshDJain]

Hello.. Sorry to Jump in between.
I am having almost same problem..

From the enclosed file, i wish the Outcome of calDate as last Column of the query.
Once its done, further calculation to be done. Correct file attached :

 

[Gasman]

For a function to return a value, the variable is the name of the function.

So as your last line in the function add

LoopOverDateRange = calDate

HTH

 

[KamleshDJain]

For a function to return a value, the variable is the name of the function.

So as your last line in the function add

LoopOverDateRange = calDate

Thanks
It worked. I was making silly mistake
HTH