Min Function Question

[Corby]

I am trying to find the minimum value of Dates in a query the are in 3
different columns and may contain nulls. How do I return the lowest date in
a single columns? I know how to in excel but can not get the syntax correct
in access.

Thanks,

Corby
Salt Lake City, UT

 

[Sprinks]

Hi, Corby.

In Query By Example view, create a calculated field (MinDate in this
example) and set it to the result of the Min() function:

MinDate: Min([MyDate])

The equivalent SQL is:

SELECT Min([MyDate]) AS MinDate
FROM YourTable;

HTH
Sprinks

 

[Corby]

Will that only return a value for that column? I have 3 columns Date1 , Date
2, Date3. And I want to return a min value of those dates to NewDate.

Thanks,
Corby

Hi, Corby.

In Query By Example view, create a calculated field (MinDate in this
example) and set it to the result of the Min() function:

MinDate: Min([MyDate])

The equivalent SQL is:

SELECT Min([MyDate]) AS MinDate
FROM YourTable;

HTH
Sprinks

I am trying to find the minimum value of Dates in a query the are in 3
different columns and may contain nulls. How do I return the lowest date in
a single columns? I know how to in excel but can not get the syntax correct
in access.

Thanks,

Corby
Salt Lake City, UT

 

[Sprinks]

Hi, Corby.

Sorry; I missed that you wanted the overall minimum. As far as I know,
there is no Access function analogous to the Min() function in VBA for Excel
that returns the lowest value in a series of numbers. I suggest you create
your own and place it in a public module:

Public Function MyMin(dte1 As Date, dte2 As Date, dte3 As Date) As Date
If dte1 <= dte2 Then
If dte1 <= dte3 Then
MyMin = dte1
End If
Else
If dte2 <= dte3 Then
MyMin = dte2
Else: MyMin = dte3
End If
End If
End Function

To assign the value to a form control, pass it the values returned from the
DMin() function, which will find the minimum in each column:

=MyMin(DMin("[YourDate1]","YourTableâ€),DMin("[YourDate2]","YourTable"),DMin("[YourDate3]","YourTable"))

Hope that helps.
Sprinks

Will that only return a value for that column? I have 3 columns Date1 , Date
2, Date3. And I want to return a min value of those dates to NewDate.

Thanks,
Corby

Hi, Corby.

In Query By Example view, create a calculated field (MinDate in this
example) and set it to the result of the Min() function:

MinDate: Min([MyDate])

The equivalent SQL is:

SELECT Min([MyDate]) AS MinDate
FROM YourTable;

HTH
Sprinks

I am trying to find the minimum value of Dates in a query the are in 3
different columns and may contain nulls. How do I return the lowest date in
a single columns? I know how to in excel but can not get the syntax correct
in access.

Thanks,

Corby
Salt Lake City, UT