Min Max dates not consistent
in [General]
|
From: Allen Browne on 14 May 2010 21:46 Did you try CVDate(), as suggested in the article? -- 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:3F86033F-E661-4EC2-865E-3DBF71498EE5(a)microsoft.com... > 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 >> >> >> >> . >> >> >> . >> |