Min Max dates not consistent

  • Thread starter Thread starter Bernie
  • Start date Start date
B

Bernie

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
 
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,
As always thanks.
Your right there are lots of null values
And yes it's left aligned, as text
Bernie
Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
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
Click to expand...

.
Click to expand...
 
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

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
Allen,
As always thanks.
Your right there are lots of null values
And yes it's left aligned, as text
Bernie
Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
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
Click to expand...

.
Click to expand...
Click to expand...
 
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
Allen,
As always thanks.
Your right there are lots of null values
And yes it's left aligned, as text
Bernie
Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.


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

.
Click to expand...
Click to expand...
.
Click to expand...
 
Did you try CVDate(), as suggested in the article?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Bernie said:
Allen,
As always thanks.
Your right there are lots of null values
And yes it's left aligned, as text
Bernie
:

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

Reply to group, rather than allenbrowne at mvps dot org.


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

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top