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:
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.
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:] & "*"));