Finding the lowest date

[Drago1920]

Hello everyone,

I have a database to track employee training, and I have created the module below 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.

 

[dhookom]

The correct values should return regardless of year. I would use:

Function FirstLow(D1 as Date, D2 as Date, D3 as Date) as Date

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

You want "to find the lowest date from multiple fields" in a table in Access DB, right?

Why not:

[pre]
Select MIN(MyDate) As [red]FirstLowDate[/red] From
(Select MIN([blue]LandDate[/blue]) As MyDate From ATable
Where [green]<some conditions>[/green]
UNION
Select MIN([blue]HoverDate[/blue]) From ATable
Where [green]<some conditions>[/green]
UNION
Select MIN([blue]DepDate[/blue]) From ATable
Where [green]<some conditions>[/green])
[/pre]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson