Min of Dates

pdvsa
pdvsa used Ask the Experts™
on
Experts, I have a report that groups Dates as in below.  But now I want to change this based on the min of ([tblTransactions].[LatestShipDate]), tblLetterOfCredit].[FinalMaturity], tblLetterOfCredit].[InitialExpireDate] and to keep the formatting as "mmmm yyyy".

How to do this?  thank you

Here is what I am using now but want to make the change as stated:
Dates: IIf(IsNull([tblTransactions].[LatestShipDate]),Format([tblLetterOfCredit].[FinalMaturity],"mmmm yyyy"),Format([tblTransactions.LatestShipDate],"mmmm yyyy"))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
You could use Switch:

Dates: Switch([tblTransactions].[LatestShipDate] <= [tblLetterOfCredit].[FinalMaturity] And [tblTransactions].[LatestShipDate] <= [tblLetterOfCredit].[InitialExpireDate], [tblTransactions].[LatestShipDate], [tblLetterOfCredit].[FinalMaturity] <= [tblTransactions].[LatestShipDate] And [tblLetterOfCredit].[FinalMaturity] <= [tblLetterOfCredit].[InitialExpireDate], [tblLetterOfCredit].[FinalMaturity], True, [tblLetterOfCredit].[InitialExpireDate])

For more about Switch, please see:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
Top Expert 2010
Commented:
Or, you could use my RowStats UDF, as defined in http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.html

Add this UDF:

Function RowStats(Stat As String, ParamArray Vars())
    
    ' Function by Patrick Matthews
    
    ' This code may be used and distributed freely, so long as you attribute authorship, and indicate
    ' what URL you found the code at
    
    ' This Access UDF calculates various stats for the values passed into the ParamArray.  It
    ' was originally designed for passing several values from a particular row set for
    ' evaluation; since the values come from the same row, the usual aggregate functions would
    ' not be appropriate.
    
    ' The Stat argument (NOT case sensitive) determines what statistic is calculated:
    ' "count":          count of non-null values
    ' "min", "max":     minimum or maximum
    ' "sum", "avg":     sum or average, excluding strings
    ' "var", "stdev":   sample variance or standard deviation (excluding strings)
    ' "varp", "stdevp": population variance or standard deviation (excluding strings)
    
    ' You may pass any value in the ParamArray.  Strings, nulls, and dates are ignored for the
    ' sum, avg, stdev, stdevp, var, and varp calculations.  (Thus, you could use this function to
    ' add several columns together without wrapping each column with Nz() to handle nulls...)
    
    ' If run from VBA, this function appears to accept any number of values in the Vars argument;
    ' I successfully tested passing several hundred values.  If run from the Access query editor,
    ' the limit appears to be about 28 values.  If you need to process more than 28 values, you
    ' should use the related function RowStatsFieldList.  (You can overcome this limit by embedding
    ' RowStats expressions, but this will produce potentially erroneous results for the Avg,
    ' StDev[P], or Var[P] stats
    
    ' Please note that you can pass arrays as elements of the Vars ParamArray; indeed, the
    ' RowStatsFieldList function does that
    
    ' As with the regular aggregate functions, if all of the parameters are null, then the
    ' return value is null, except on count, where the return would be zero
    
    Dim Numerator As Double
    Dim Denominator As Double
    Dim Counter As Long, Counter2 As Long
    Dim Result As Variant
    Dim Mean As Double
    
    ' Force to upper case to make sure string comparisons are always performed as expected;
    ' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default
    
    Stat = UCase(Stat)
    
    Select Case Stat
    
        ' In each Case below, loop through the elements of the Vars ParamArray.  If the element
        ' is itself an array, then loop through its elements
        
        Case "COUNT"
        
            ' Increment the result for each non-null value in the array
            
            Result = CLng(0)
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If Not IsNull(Vars(Counter)) Then Result = Result + 1
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If Not IsNull(Vars(Counter)(Counter2)) Then Result = Result + 1
                    Next
                End If
            Next
            
        Case "MIN"
        
            ' Initialize the result to Null, then check all non-Null values in turn to see
            ' if it is less.
            
            Result = Null
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                        Result = Vars(Counter)
                    ElseIf Vars(Counter) < Result Then
                        Result = Vars(Counter)
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
                            Result = Vars(Counter)(Counter2)
                        ElseIf Vars(Counter)(Counter2) < Result Then
                            Result = Vars(Counter)(Counter2)
                        End If
                    Next
                End If
            Next
                      
        Case "MAX"
            
            ' Initialize the result to Null, then check all non-Null values in turn to see
            ' if it is greater.
            
            Result = Null
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                        Result = Vars(Counter)
                    ElseIf Vars(Counter) > Result Then
                        Result = Vars(Counter)
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
                            Result = Vars(Counter)(Counter2)
                        ElseIf Vars(Counter)(Counter2) > Result Then
                            Result = Vars(Counter)(Counter2)
                        End If
                    Next
                End If
            Next
            
        Case "AVG", "SUM"
        
            ' Check each value in turn.  If it is numeric, then increment numerator and denominator.
            ' Divide numerator by denominator to get an average, or by 1 to get the sum.  Any Date
            ' values are coerced into Double
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNumeric(Vars(Counter)) Then
                        Numerator = Numerator + Vars(Counter)
                        Denominator = Denominator + 1
                    ElseIf IsDate(Vars(Counter)) Then
                        Numerator = Numerator + CDbl(Vars(Counter))
                        Denominator = Denominator + 1
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNumeric(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + Vars(Counter)(Counter2)
                            Denominator = Denominator + 1
                        ElseIf IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
                            Denominator = Denominator + 1
                        End If
                    Next
                End If
            Next
            
            If Denominator > 0 Then
                Result = Numerator / IIf(Stat = "AVG", Denominator, 1)
            Else
                Result = Null
            End If
            
        Case "STDEV", "STDEVP", "VAR", "VARP"
        
            ' Take one pass through the set to determine the average, and then determine the
            ' sum of squared deviances from the mean.  Divide by number of elements in the
            ' array for population or (elements - 1) for sample.  If standard deviation,
            ' take square root.  Any Date values are coerced into Double
            
            ' This pass generates the numerator and denominator needed for the average
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNumeric(Vars(Counter)) Then
                        Numerator = Numerator + Vars(Counter)
                        Denominator = Denominator + 1
                    ElseIf IsDate(Vars(Counter)) Then
                        Numerator = Numerator + CDbl(Vars(Counter))
                        Denominator = Denominator + 1
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNumeric(Vars(Counter)(Counter2)) Or IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + Vars(Counter)(Counter2)
                            Denominator = Denominator + 1
                        ElseIf IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
                            Denominator = Denominator + 1
                        End If
                    Next
                End If
            Next
            
            ' Make sure there are enough numeric elements to avoid a division by zero error.  If not,
            ' return Null
            
            If (Stat Like "*P" And Denominator > 0) Or (Not Stat Like "*P" And Denominator > 1) Then
                
                Mean = Numerator / Denominator
                
                ' This pass sums the squares of the differences between each data point and the mean
                
                For Counter = LBound(Vars) To UBound(Vars)
                    If Not IsArray(Vars(0)) Then
                        If IsNumeric(Vars(Counter)) Then
                            Result = Result + (Vars(Counter) - Mean) ^ 2
                        ElseIf IsDate(Vars(Counter)) Then
                            Result = Result + (CDbl(Vars(Counter)) - Mean) ^ 2
                        End If
                    Else
                        For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                            If IsNumeric(Vars(Counter)(Counter2)) Then
                                Result = Result + (Vars(Counter)(Counter2) - Mean) ^ 2
                            ElseIf IsDate(Vars(Counter)(Counter2)) Then
                                Result = Result + (CDbl(Vars(Counter)(Counter2)) - Mean) ^ 2
                            End If
                        Next
                    End If
                Next
                
                ' Divide by N for population, and N-1 for sample
                
                If Stat Like "*P" Then
                    Result = Result / Denominator
                Else
                    Result = Result / (Denominator - 1)
                End If
            
                ' Take square root if standard deviation
                
                If Stat Like "S*" Then Result = Result ^ 0.5
            
            Else
                Result = Null
            End If
            
        Case Else
            
            ' If Stat is none of the above, then return Null -- invalid Stat
            
            Result = Null
    End Select
    
    ' Set return value
    
    If Not IsNull(Result) Then RowStats = Result Else RowStats = Null
    
End Function

Open in new window


Then use it in a query like this:

SELECT [tblTransactions].[LatestShipDate]), [tblLetterOfCredit].[FinalMaturity], [tblLetterOfCredit].[InitialExpireDate], 
    RowStats("Min", [tblTransactions].[LatestShipDate]), [tblLetterOfCredit].[FinalMaturity], [tblLetterOfCredit].[InitialExpireDate]) AS Dates
FROM <your from clause here>

Open in new window

pdvsaProject finance

Author

Commented:
whoa... I will check this out.  thank you...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

pdvsaProject finance

Author

Commented:
Patrick, pretty crafty.  I think the module is way above me and not sure how to incorporate in the report.  

I used the SWITCH.  However, it seems to select a MIN of " " if one of the dates are Null.  I would need for it to exclude a Null and just select the MIN out of any values that are NOT NULL.  

I hope that makes sense.   thank you. If the Module takes care of this then I might want to use the Module.. let me know.

thank you.
Top Expert 2010

Commented:
I would have thought that Switch could handle that.  Even if not, then the VBA approach should have no trouble with the nulls.

Can you post a smallish sample file?
pdvsaProject finance

Author

Commented:
I somehow missed the post.  Will try to post tom am..   Thanks
pdvsaProject finance

Author

Commented:
Patrick:  attached is a pared down version.  The autoexec opens and click "year" then the report button.  I have the module in as well.  I am thinking the module is the way to go.   There is only 1 report in the db.   Thank you for the help.  attached is 2k7.
New---use-this-for-EE.zip
Top Expert 2010
Commented:
OK, I see why the nulls are killing Switch: whenever you compare something to a null, you always get false.  (Even null = null returns false!)

So, the RowStats approach will be golden.  To make Switch work, you have to handle the nulls like so:

Switch([finalmaturity]<=Nz([LatestShipDate],#12/31/9999#) And [finalmaturity]<=Nz([InitialExpireDate],#12/31/9999#),[finalmaturity],[LatestShipDate]<=Nz([InitialExpireDate],#12/31/9999#),[LatestShipDate],True,[InitialExpireDate])
pdvsaProject finance

Author

Commented:
Its like reading Chinese to me.   You are too good.   Thanks a mill... Will test when get to computer.
pdvsaProject finance

Author

Commented:
I think i get it now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial