Solved Low Date not seeing the previous year (1 Viewer)

[Drago1920]

Hello everyone,

I have a database to track employee training and a module to find the lowest date from multiple fields. It is working well until the dates are from the previous year. Is there a way to include the previous years? Or please tell me what I did wrong.

Module format

Function FirstLow(D1, D2, D3)
D1 = Nz(D1, #1/1/2999#)
D2 = Nz(D2, #1/1/2999#)
D3 = Nz(D3, #1/1/2999#)

If D1 < D2 And D1 < D3 Then
FirstLow = D1
ElseIf D2 < D1 And D2 < D3 Then
FirstLow = D2
Else
FirstLow = D3
End If

Query

First: FirstLow([LandDate],[HoverDate],[DepDate])



Thank you for your time.

 

[pbaldy]

I suspect the Nz() function is causing the variables to be seen as text. Try

D1 = CDate(Nz(D1, #1/1/2999#))

 

[CJ_London]

until the dates are from the previous year.

works for me

?firstlow(#01/01/2023#,null,#01/01/2024#)
01/01/2023

?firstlow(null,#01/01/2023#,#01/01/2024#)
01/01/2023

?firstlow(#01/01/2023#,#01/01/2022#,#01/01/2024#)
01/01/2022

perhaps your query is excluding the previous year

 

[arnelgp]

you just test 2 at a time:

Function FirstLow(D1, D2, D3)
Dim Ret As Date
    D1 = Nz(D1, #1/1/2999#)
    D2 = Nz(D2, #1/1/2999#)
    D3 = Nz(D3, #1/1/2999#)
Ret = D1
If D2 < Ret  Then
    Ret = D2
end If
If D3 < Ret Then
    Ret = D3
End If
FirstLow =  Ret
End Function

 

[pbaldy]

I'd use this which would be more flexible (any number of inputs):

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

 

[MarkK]

You can also do...

Function GetLeastDate(ParamArray dates()) As Date
    Dim var
        
    With CreateObject("System.Collections.ArrayList")
        For Each var In dates
            If IsDate(var) Then .Add CDate(var)
        Next
        .Sort
        GetLeastDate = .Item(0)
    End With
End Function

This creates a late-bound mscorlib.ArrayList, adds dates, sorts them ASC, and returns the first.

 

[Drago1920]

I suspect the Nz() function is causing the variables to be seen as text. Try

D1 = CDate(Nz(D1, #1/1/2999#))

Thank you pbaldy that was the problem. It is working now.

Thank you all for the response.

 

[pbaldy]

Thank you@ pbaldy that was the problem. It is working now.

Happy to help!

 

[Pat Hartman]

I have a database to track employee training and a module to find the lowest date from multiple fields. It is working well until the dates are from the previous year. Is there a way to include the previous years? Or please tell me what I did wrong.

Paul provided an answer to your question but to solve the actual problem, you need to normalize your schema. You have what is called a "repeating group" where the same piece of data is represented in multiple columns (a series of dates). Things that occur multiple times need to be stored in a separate table with one row for each item.