Min Max dates not consistent

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
 
A

Allen Browne

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?
 
B

Bernie

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...
 
A

Allen Browne

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...
 
B

Bernie

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...
 
A

Allen Browne

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Min/Max Dates 1
Min/Max Dates only looking at month? 1
Period End Dates Table ... How to Link? 3
Min/Max Date between 2 diff years? 2
Excel won't go away 10
SUMPRODUCT 3
Grouping/Deleting Dates/Times (HELP ANYONE!) 2
Macro to select rows based on dates 11

Top