=Format(Date(), "mm yy") does not display monthly data?

I am trying to get my query to compile our current monthly pass rates.
The above Criteria does help in displaying pass rates on an associated
report, but they are not the current month.
What is wrong with my Criteria?

Dear Paul:

The Format() function creates a text value. To use this text value to order
or to compare rows is probably not what you intend.

For example, which is greater:

12 05
1 06

When the above are compared as text, 12 05 is definitely the greater. Not
so when they are compared as dates! 2 comes after "space." And if you
have:

12 05
01 06

then 2 comes after 1, so as text, 12 05 still comes AFTER 01 06. When you
make this a text value, the order of values is all screwed up.

Perhaps what you mean is:

Year(Date2) Year(Date1) OR (Year(Date2) = Year(Date1) AND Month(Date2) =
Month(Date1))

Something like this probably would do what I'm pretty sure you intend. It
says that, if the NUMERIC value of the Year component is greater, then the
date is greater. If they're the same, then compare the NUMERIC month
values. If that is greater, or equal, then the test is TRUE. Otherwise,
it's FALSE.

A way to actually make it work correctly as a text comparison is to put the
MORE SIGNIFICANT year value first, along with making sure the values show 2
digits. In this comparison:

05 12
06 01

the comparison would always function correctly.

Not an uncommon difficulty. We've seen people who expect Jan to come before
Feb as well. Our brains KNOW to compare the months they represent. The
computer will only use the alphabetic order to make the comparison. Another
version of the same "human blindspot" syndrome. We're intelligent,
computers can be idiotically literal. That's all they're designed to be
able to do. Very little associative capacity, and no intuition.

Tom Ellison


"Paul Casavant" Paul wrote in message
...
I am trying to get my query to compile our current monthly pass rates.
The above Criteria does help in displaying pass rates on an associated
report, but they are not the current month.
What is wrong with my Criteria?

I have the above criteria in a "Pass Rate to Date" type query.
In the "Date" field of the query, I have the "Where" in the Total block
followed by =Format(Date(), "mm yy") in the Criteria block.
The current result is when anyone opens our QA database, it displays the
current pass rates for each section, it's just not accurate according to just
the 1-14 Dec data. The goal is to get the query to calculate all pass rates
from the first of the current month.
Will swapping the "mm yy" to "yy mm" work?

"Tom Ellison" wrote:

Dear Paul:

The Format() function creates a text value. To use this text value to order
or to compare rows is probably not what you intend.

For example, which is greater:

12 05
1 06

When the above are compared as text, 12 05 is definitely the greater. Not
so when they are compared as dates! 2 comes after "space." And if you
have:

12 05
01 06

then 2 comes after 1, so as text, 12 05 still comes AFTER 01 06. When you
make this a text value, the order of values is all screwed up.

Perhaps what you mean is:

Year(Date2) Year(Date1) OR (Year(Date2) = Year(Date1) AND Month(Date2) =
Month(Date1))

Something like this probably would do what I'm pretty sure you intend. It
says that, if the NUMERIC value of the Year component is greater, then the
date is greater. If they're the same, then compare the NUMERIC month
values. If that is greater, or equal, then the test is TRUE. Otherwise,
it's FALSE.

A way to actually make it work correctly as a text comparison is to put the
MORE SIGNIFICANT year value first, along with making sure the values show 2
digits. In this comparison:

05 12
06 01

the comparison would always function correctly.

Not an uncommon difficulty. We've seen people who expect Jan to come before
Feb as well. Our brains KNOW to compare the months they represent. The
computer will only use the alphabetic order to make the comparison. Another
version of the same "human blindspot" syndrome. We're intelligent,
computers can be idiotically literal. That's all they're designed to be
able to do. Very little associative capacity, and no intuition.

Tom Ellison


"Paul Casavant" Paul wrote in message
...
I am trying to get my query to compile our current monthly pass rates.
The above Criteria does help in displaying pass rates on an associated
report, but they are not the current month.
What is wrong with my Criteria?

I did swap the "mm yy" to "yy mm" and it didn't match the monthly data.
BUT, I changed it to "yyyy mm" and it WORKS!!
Will this change work when we go into 2006?

"Tom Ellison" wrote:

Dear Paul:

The Format() function creates a text value. To use this text value to order
or to compare rows is probably not what you intend.

For example, which is greater:

12 05
1 06

When the above are compared as text, 12 05 is definitely the greater. Not
so when they are compared as dates! 2 comes after "space." And if you
have:

12 05
01 06

then 2 comes after 1, so as text, 12 05 still comes AFTER 01 06. When you
make this a text value, the order of values is all screwed up.

Perhaps what you mean is:

Year(Date2) Year(Date1) OR (Year(Date2) = Year(Date1) AND Month(Date2) =
Month(Date1))

Something like this probably would do what I'm pretty sure you intend. It
says that, if the NUMERIC value of the Year component is greater, then the
date is greater. If they're the same, then compare the NUMERIC month
values. If that is greater, or equal, then the test is TRUE. Otherwise,
it's FALSE.

A way to actually make it work correctly as a text comparison is to put the
MORE SIGNIFICANT year value first, along with making sure the values show 2
digits. In this comparison:

05 12
06 01

the comparison would always function correctly.

Not an uncommon difficulty. We've seen people who expect Jan to come before
Feb as well. Our brains KNOW to compare the months they represent. The
computer will only use the alphabetic order to make the comparison. Another
version of the same "human blindspot" syndrome. We're intelligent,
computers can be idiotically literal. That's all they're designed to be
able to do. Very little associative capacity, and no intuition.

Tom Ellison


"Paul Casavant" Paul wrote in message
...
I am trying to get my query to compile our current monthly pass rates.
The above Criteria does help in displaying pass rates on an associated
report, but they are not the current month.
What is wrong with my Criteria?