Date Data Type problem (1 Viewer)

selenau837

Can still see y'all......
Local time
Today, 10:21
Joined
Aug 26, 2005
Messages
2,211
Below is a function that is in my database.

It has a Date Datatype. The problem I am running into is when all the arguments that are passed are null, it is returning a 12:00AM value.

I read in the help files that:
When other numeric types are converted to Date, values to the left of the decimal represent date information while values to the right of the decimal represent time. Midnight is 0 and midday is 0.5. Negative whole numbers represent dates before 30 December 1899.

I'm assuming it is changing to midnight becuase 0 is being returned.

My question is, how can I stop it from doing that. I have a query that uses this function. I dont' want it to return Midnight. How do I stop that?

I have posted my SQL statment below the function.

Any help would be appreciated.





Code:
Public Function CompareDates(Date1, Date2, Optional date3, Optional date4, _
    Optional date5, Optional date6) As Date

    If IsDate(Date1) Then
        CompareDates = Date1
    ElseIf IsDate(Date2) Then
        CompareDates = Date2
    ElseIf IsDate(date3) Then
        CompareDates = date3
    ElseIf IsDate(date4) Then
        CompareDates = date4
    ElseIf IsDate(date5) Then
        CompareDates = date5
    ElseIf IsDate(date6) Then
        CompareDates = date6
    End If
        
    If IsMissing(Date2) = False Then
        If Date2 > CompareDates Then
            CompareDates = Date2
        End If
    End If
    
    If IsMissing(date3) = False Then
        If date3 > CompareDates Then
            CompareDates = date3
        End If
    End If
    
    If IsMissing(date4) = False Then
        If date4 > CompareDates Then
            CompareDates = date4
        End If
    End If

    If IsMissing(date5) = False Then
        If date5 > CompareDates Then
            CompareDates = date5
        End If
    End If
    
    If IsMissing(date6) = False Then
        If date6 > CompareDates Then
            CompareDates = date6
        End If
    End If
    
End Function

Code:
SELECT tblGroupInformation.strGroupName, tblGroupInformation.strGroupNumber, tblGroupInformation.dtmEffectiveDate, tblGroupInformation.dtmDateMembershipReceived, tblGroupInformation.dtmDateOLReceived, tblGroupInformation.strNRC, tblBooksAndContracts.dtmASODraftsSent, tblBooksAndContracts.dtmBookShipped, tblBooksAndContracts.dtmContractDistributedToMarketing, [B][COLOR="Red"]CompareDates([dtmContractDistributedToMarketing],[dtmBookShipped],[dtmASODraftsSent]) AS [Complete Date][/COLOR][/B]FROM tblBooksAndContracts INNER JOIN tblGroupInformation ON tblBooksAndContracts.intTrackingNumber = tblGroupInformation.intTrackingNumber
WHERE (((tblGroupInformation.dtmEffectiveDate) Between [Enter start date:] And [Enter end date:]) AND ((tblGroupInformation.strNRC) Like [Enter action type:] & "*")) OR (((tblGroupInformation.dtmEffectiveDate) Between [Enter start date:] And [Enter end date:]) AND ((tblGroupInformation.strNRC) Like [Enter action type:] & "*")) OR (((tblGroupInformation.dtmEffectiveDate) Between [Enter start date:] And [Enter end date:]) AND ((tblGroupInformation.strNRC) Like [Enter action type:] & "*")) OR (((tblGroupInformation.dtmEffectiveDate) Between [Enter start date:] And [Enter end date:]) AND ((tblGroupInformation.strNRC) Like [Enter action type:] & "*"));
 
R

Rich

Guest
Why have you used the IsDate function and the further on IsMissing?
 

MarkK

bit cruncher
Local time
Today, 07:21
Joined
Mar 17, 2004
Messages
8,178
This might offer some ideas, but I think the only way to get around the 12:00pm issue for a zero date is to not use a date. If your function returns a string where IsDate(string) = True, then you can convert the string back to a date easily enough.

Code:
Public Function CompareDates(ParamArray dates()) As String
[COLOR="Green"]'  returns a string representing the largest date found in a list of variants
'  or an empty string if no dates were supplied[/COLOR]
   Dim i As Integer
   Dim d As Variant
   
[COLOR="Green"]   'traverse the array[/COLOR]
   For i = 0 To UBound(dates)
[COLOR="Green"]      'if current element is a date[/COLOR]
      If IsDate(dates(i)) Then
[COLOR="Green"]         'if the current element is the first date[/COLOR]
         If IsNull(d) Then
[COLOR="Green"]            'then assign to d, for further comparison[/COLOR]
            d = dates(i)
[COLOR="Green"]         'the current element is not the first date[/COLOR]
         Else
[COLOR="Green"]            'so compare it to the greatest we've found, and assign if greater[/COLOR]
            If dates(i) > d Then d = dates(i)
         End If
      End If
   Next i
   
[COLOR="Green"]   'return as formatted string, or empty string[/COLOR]
   CompareDates = IIf(IsDate(d), Format(d, "mm/dd/yyyy"), "")

End Function

Call this function using any number of parameters of any type!
 

selenau837

Can still see y'all......
Local time
Today, 10:21
Joined
Aug 26, 2005
Messages
2,211
Rich said:
Why have you used the IsDate function and the further on IsMissing?


I didn't create this, and the guy that did is gone. I don't fully understand it, but I am just trying to make it work like it should.


Lagolt---Am I to use that code you gave me in place of the function I currently have.

I see you commented it, but don't understand it.
 

MarkK

bit cruncher
Local time
Today, 07:21
Joined
Mar 17, 2004
Messages
8,178
Sel, it's your call. My idea for dealing with the 12:00 PM zero date issue is to use a string rather than a date. The function you supplied looks inefficient so I wrote a tighter one that finds the greatest date that you supply, and returns it as a string. What part of it don't you get?
Code:
Debug.Print CompareDates(#1/1/04#, #1/1/06#, #1/1/05#)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null, Null, Null, Null, #1/1/06#, Null)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null)
'returns ""

Debug.Print CompareDates(24, Null, "Hello", "1/1/2006", "1/2/2007", 34.55)
'returns "01/02/2007"
 

selenau837

Can still see y'all......
Local time
Today, 10:21
Joined
Aug 26, 2005
Messages
2,211
lagbolt said:
Sel, it's your call. My idea for dealing with the 12:00 PM zero date issue is to use a string rather than a date. The function you supplied looks inefficient so I wrote a tighter one that finds the greatest date that you supply, and returns it as a string. What part of it don't you get?
Code:
Debug.Print CompareDates(#1/1/04#, #1/1/06#, #1/1/05#)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null, Null, Null, Null, #1/1/06#, Null)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null)
'returns ""

Debug.Print CompareDates(24, Null, "Hello", "1/1/2006", "1/2/2007", 34.55)
'returns "01/02/2007"


*light bulb comes on over head* DING! I get it now.

I guess because I really couldn't follow his code I didn't understand fully what the function was doing. But seeing your, and your now explination, I now understand.

Yes yours is much better than his.

Thank you ohhh so much.
 

selenau837

Can still see y'all......
Local time
Today, 10:21
Joined
Aug 26, 2005
Messages
2,211
lagbolt said:
Sel, it's your call. My idea for dealing with the 12:00 PM zero date issue is to use a string rather than a date. The function you supplied looks inefficient so I wrote a tighter one that finds the greatest date that you supply, and returns it as a string. What part of it don't you get?
Code:
Debug.Print CompareDates(#1/1/04#, #1/1/06#, #1/1/05#)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null, Null, Null, Null, #1/1/06#, Null)
'returns "01/01/2006"

Debug.Print CompareDates(Null, Null)
'returns ""

Debug.Print CompareDates(24, Null, "Hello", "1/1/2006", "1/2/2007", 34.55)
'returns "01/02/2007"

I understand. The lightbulb has come on.

Thank you, I've updated my function with yours.

Thank you so much!!
 

Users who are viewing this thread

Top Bottom