Min Max dates not consistent
in [General]
|
From: Bernie on 14 May 2010 01:32 For each record I have 2 columns with dates and need to identify the min or max from both. Using: MinDate: IIf([CalloutDaystoDuefh]<[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) MaxDate: IIf([CalloutDaystoDuefh]>[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) For the most part the results are correct but below is an example of the issue, second record is reverse CalloutDaystoDuefh CalloutDaystoDueFC MinDate MaxDate 9/12/2015 9/7/2016 9/12/2015 9/7/2016 9/13/2019 8/12/2020 "8/12/2020" 9/13/2019 Thanks in advance Bernie
From: Allen Browne on 14 May 2010 01:54 The most likely answer is that Access is misunderstanding the data types, as explained here: http://allenbrowne.com/ser-45.html Are both of these Date/Time fields? Are there lots of records where both dates are null? When you view the query output, is the text in the MinDate and MaxDate shown left-aligned (like text), or right-aligned (like numbers and dates)? For testing, try adding another calculated field like this: ([CalloutDaystoDuefh] > [CalloutDaystoDueFC]) This should yield results of -1 (true), 0 (false), or Null. Does this help pin down how Access is understanding the data? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message news:F00AE85A-8320-4675-A02E-C7835D21391C(a)microsoft.com... > For each record I have 2 columns with dates and need to identify the min > or > max from both. > Using: > MinDate: > IIf([CalloutDaystoDuefh]<[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > MaxDate: > IIf([CalloutDaystoDuefh]>[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > For the most part the results are correct but below is an example of the > issue, second record is reverse > CalloutDaystoDuefh CalloutDaystoDueFC MinDate MaxDate > 9/12/2015 9/7/2016 9/12/2015 9/7/2016 > 9/13/2019 8/12/2020 "8/12/2020" 9/13/2019 > Thanks in advance > Bernie
From: Bernie on 14 May 2010 02:51 Allen, As always thanks. Your right there are lots of null values And yes it's left aligned, as text Bernie "Allen Browne" wrote: > The most likely answer is that Access is misunderstanding the data types, as > explained here: > http://allenbrowne.com/ser-45.html > > Are both of these Date/Time fields? > Are there lots of records where both dates are null? > When you view the query output, is the text in the MinDate and MaxDate shown > left-aligned (like text), or right-aligned (like numbers and dates)? > > For testing, try adding another calculated field like this: > ([CalloutDaystoDuefh] > [CalloutDaystoDueFC]) > This should yield results of -1 (true), 0 (false), or Null. > Does this help pin down how Access is understanding the data? > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message > news:F00AE85A-8320-4675-A02E-C7835D21391C(a)microsoft.com... > > For each record I have 2 columns with dates and need to identify the min > > or > > max from both. > > Using: > > MinDate: > > IIf([CalloutDaystoDuefh]<[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > > MaxDate: > > IIf([CalloutDaystoDuefh]>[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > > For the most part the results are correct but below is an example of the > > issue, second record is reverse > > CalloutDaystoDuefh CalloutDaystoDueFC MinDate MaxDate > > 9/12/2015 9/7/2016 9/12/2015 9/7/2016 > > 9/13/2019 8/12/2020 "8/12/2020" 9/13/2019 > > Thanks in advance > > Bernie > > . >
From: Allen Browne on 14 May 2010 04:27 Okay, JET can get lost trying to guess the data type where the values are largely null. An old trick is to use IIf() to specify the type you want for a condition that cannot occur, e.g: MinDate: IIf(False, #1/1/1900#, IIf([CalloutDaystoDuefh] < [CalloutDaystoDueFC], [CalloutDaystoDuefh], [CalloutDaystoDueFC])) Since False is never True, the 1900 date is never assigned, but you've hinted that the data is of type Date/Time. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message news:73F880E6-469B-4E67-B12C-E1D37EBC056E(a)microsoft.com... > Allen, > As always thanks. > Your right there are lots of null values > And yes it's left aligned, as text > Bernie > "Allen Browne" wrote: > >> The most likely answer is that Access is misunderstanding the data types, >> as >> explained here: >> http://allenbrowne.com/ser-45.html >> >> Are both of these Date/Time fields? >> Are there lots of records where both dates are null? >> When you view the query output, is the text in the MinDate and MaxDate >> shown >> left-aligned (like text), or right-aligned (like numbers and dates)? >> >> For testing, try adding another calculated field like this: >> ([CalloutDaystoDuefh] > [CalloutDaystoDueFC]) >> This should yield results of -1 (true), 0 (false), or Null. >> Does this help pin down how Access is understanding the data? >> >> -- >> Allen Browne - Microsoft MVP. Perth, Western Australia >> Tips for Access users - http://allenbrowne.com/tips.html >> Reply to group, rather than allenbrowne at mvps dot org. >> >> >> "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message >> news:F00AE85A-8320-4675-A02E-C7835D21391C(a)microsoft.com... >> > For each record I have 2 columns with dates and need to identify the >> > min >> > or >> > max from both. >> > Using: >> > MinDate: >> > IIf([CalloutDaystoDuefh]<[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) >> > MaxDate: >> > IIf([CalloutDaystoDuefh]>[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) >> > For the most part the results are correct but below is an example of >> > the >> > issue, second record is reverse >> > CalloutDaystoDuefh CalloutDaystoDueFC MinDate MaxDate >> > 9/12/2015 9/7/2016 9/12/2015 9/7/2016 >> > 9/13/2019 8/12/2020 "8/12/2020" 9/13/2019 >> > Thanks in advance >> > Bernie >> >> . >>
From: Bernie on 14 May 2010 11:17
Allen, That didn't shake the system. Took the original query that did the calculation and did a make table, changed fields to date/time. Built new query with 1/1/1900 iif and it appears to be working. Would still be interested in find a way not to "make table" Thanks again Bermie "Allen Browne" wrote: > Okay, JET can get lost trying to guess the data type where the values are > largely null. An old trick is to use IIf() to specify the type you want for > a condition that cannot occur, e.g: > > MinDate: IIf(False, #1/1/1900#, IIf([CalloutDaystoDuefh] < > [CalloutDaystoDueFC], [CalloutDaystoDuefh], [CalloutDaystoDueFC])) > > Since False is never True, the 1900 date is never assigned, but you've > hinted that the data is of type Date/Time. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message > news:73F880E6-469B-4E67-B12C-E1D37EBC056E(a)microsoft.com... > > Allen, > > As always thanks. > > Your right there are lots of null values > > And yes it's left aligned, as text > > Bernie > > "Allen Browne" wrote: > > > >> The most likely answer is that Access is misunderstanding the data types, > >> as > >> explained here: > >> http://allenbrowne.com/ser-45.html > >> > >> Are both of these Date/Time fields? > >> Are there lots of records where both dates are null? > >> When you view the query output, is the text in the MinDate and MaxDate > >> shown > >> left-aligned (like text), or right-aligned (like numbers and dates)? > >> > >> For testing, try adding another calculated field like this: > >> ([CalloutDaystoDuefh] > [CalloutDaystoDueFC]) > >> This should yield results of -1 (true), 0 (false), or Null. > >> Does this help pin down how Access is understanding the data? > >> > >> -- > >> Allen Browne - Microsoft MVP. Perth, Western Australia > >> Tips for Access users - http://allenbrowne.com/tips.html > >> Reply to group, rather than allenbrowne at mvps dot org. > >> > >> > >> "Bernie" <Bernie(a)discussions.microsoft.com> wrote in message > >> news:F00AE85A-8320-4675-A02E-C7835D21391C(a)microsoft.com... > >> > For each record I have 2 columns with dates and need to identify the > >> > min > >> > or > >> > max from both. > >> > Using: > >> > MinDate: > >> > IIf([CalloutDaystoDuefh]<[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > >> > MaxDate: > >> > IIf([CalloutDaystoDuefh]>[CalloutDaystoDueFC],[CalloutDaystoDuefh],[CalloutDaystoDueFC]) > >> > For the most part the results are correct but below is an example of > >> > the > >> > issue, second record is reverse > >> > CalloutDaystoDuefh CalloutDaystoDueFC MinDate MaxDate > >> > 9/12/2015 9/7/2016 9/12/2015 9/7/2016 > >> > 9/13/2019 8/12/2020 "8/12/2020" 9/13/2019 > >> > Thanks in advance > >> > Bernie > >> > >> . > >> > . > |