Control Source/Form

[John]

Hello,

I have a check box control on my form and would like to create an expression:

In the form I have 3 bound controls( Founding Source, EndDate, DropDate)

In my check box control I would like to see a check mark whenever the
funding source = 1, and EndDate is earlier than today, and the DropDate is
empty or no dropdate. I have the following expression:

=IIf([tstudentclass subform1].[Form]![FundingSource]=1,"YES" And
IIf([tstudentclass subform1].[Form]![EndDate]<=(Now()),"YES","NO" And
IIf([tstudentclass subform1].[Form]![DropDate]=null,"YES"))).

For some reason, the fundingsource works but not the other two??? Any idea
why?? OR a better solution would be great.

John

 

[Ken Sheridan]

Try this:

= ([tstudentclass subform1].[Form]![FundingSource] = 1 And [tstudentclass
subform1].[Form]![EndDate] < Date() And IsNull([tstudentclass
subform1].[Form]![DropDate]))

The expression should evaluate to True if all of the three sub-expressions
evaluate to True. Consequently the check box will be checked or unchecked as
appropriate.

You say "EndDate is earlier than today" should be true, so I've changed the
expression to < Date(). The Date function returns the date/time at midnight
at the start of the current day, whereas the Now function returns the current
date and time. If the EndDate value was today at or before the current time
of day, bearing in mind all date/time values include some time of day, even
if its zero, i.e. midnight at the start of the day, the <=Now() would
evaluate to True, but the value would not be "earlier than today".

Arising from this if you want to test for a date time value being today then
don't test for equality. If the value includes a non-zero time of day (which
might not show if the value is formatted as date only) then MyDate = Date()
will evaluate to False as the time of day returned by Date is zero. The best
way to test for values matching today's date is to test for the value being
on or after today and before the day after today, MyDate >= Date() And MyDate
< Date() + 1. Whatever the time of day in MyDate, from 00:00:00 to 23:59:59,
will be before midnight at the start of tomorrow. Another way would be to
use the DateValue function, DateValue(MyDate) = Date(). This possibility of
dates including unseen times if day frequently catches people out when
testing for dates within a range:

WHERE MyDate BETWEEN #07/04/2005# AND #12/25/2005#

will evaluate to false if MyDate is Christmas Day, but includes a non-zero
time of day, whereas:

WHERE MyDate >= #07/04/2005# AND MyDate < #12/25/2005# + 1

would evaluate to True for any date/time value from midnight at the start of
4th July this year to 23:59:59 on Christmas Day this year.

Where you'd gone wrong with the nested IIF functions BTW was in including
the Boolean AND operators. To nest IIF functions you don't need Boolean
operations; instead you use one function as the 'not true' argument of the
previous one, so if the first evaluates to False it evaluates the nest one
and so on down the line until one evaluates to True.

Also you've used the literal strings "YES" and "NO". In circumstances like
this use the Boolean values TRUE and FALSE (without quotes). Access does use
Yes and No to express Boolean values, but its better to stick with the
standard forms, TRUE and FALSE. Sometimes you'll see -1 and 0 used for TRUE
and FALSE. This is how Access implements Boolean values, but its not a good
idea to use these integer values. Relying on the implementation is regarded
as bad programming practice.

Hello,

I have a check box control on my form and would like to create an expression:

In the form I have 3 bound controls( Founding Source, EndDate, DropDate)

In my check box control I would like to see a check mark whenever the
funding source = 1, and EndDate is earlier than today, and the DropDate is
empty or no dropdate. I have the following expression:

=IIf([tstudentclass subform1].[Form]![FundingSource]=1,"YES" And
IIf([tstudentclass subform1].[Form]![EndDate]<=(Now()),"YES","NO" And
IIf([tstudentclass subform1].[Form]![DropDate]=null,"YES"))).

For some reason, the fundingsource works but not the other two??? Any idea
why?? OR a better solution would be great.

John

 

[John]

Thank you Very much Ken.

I guess I didn't need to use the IIf function? How does it now that it
should be true?

Try this:

= ([tstudentclass subform1].[Form]![FundingSource] = 1 And [tstudentclass
subform1].[Form]![EndDate] < Date() And IsNull([tstudentclass
subform1].[Form]![DropDate]))

The expression should evaluate to True if all of the three sub-expressions
evaluate to True. Consequently the check box will be checked or unchecked as
appropriate.

You say "EndDate is earlier than today" should be true, so I've changed the
expression to < Date(). The Date function returns the date/time at midnight
at the start of the current day, whereas the Now function returns the current
date and time. If the EndDate value was today at or before the current time
of day, bearing in mind all date/time values include some time of day, even
if its zero, i.e. midnight at the start of the day, the <=Now() would
evaluate to True, but the value would not be "earlier than today".

Arising from this if you want to test for a date time value being today then
don't test for equality. If the value includes a non-zero time of day (which
might not show if the value is formatted as date only) then MyDate = Date()
will evaluate to False as the time of day returned by Date is zero. The best
way to test for values matching today's date is to test for the value being
on or after today and before the day after today, MyDate >= Date() And MyDate
< Date() + 1. Whatever the time of day in MyDate, from 00:00:00 to 23:59:59,
will be before midnight at the start of tomorrow. Another way would be to
use the DateValue function, DateValue(MyDate) = Date(). This possibility of
dates including unseen times if day frequently catches people out when
testing for dates within a range:

WHERE MyDate BETWEEN #07/04/2005# AND #12/25/2005#

will evaluate to false if MyDate is Christmas Day, but includes a non-zero
time of day, whereas:

WHERE MyDate >= #07/04/2005# AND MyDate < #12/25/2005# + 1

would evaluate to True for any date/time value from midnight at the start of
4th July this year to 23:59:59 on Christmas Day this year.

Where you'd gone wrong with the nested IIF functions BTW was in including
the Boolean AND operators. To nest IIF functions you don't need Boolean
operations; instead you use one function as the 'not true' argument of the
previous one, so if the first evaluates to False it evaluates the nest one
and so on down the line until one evaluates to True.

Also you've used the literal strings "YES" and "NO". In circumstances like
this use the Boolean values TRUE and FALSE (without quotes). Access does use
Yes and No to express Boolean values, but its better to stick with the
standard forms, TRUE and FALSE. Sometimes you'll see -1 and 0 used for TRUE
and FALSE. This is how Access implements Boolean values, but its not a good
idea to use these integer values. Relying on the implementation is regarded
as bad programming practice.

Hello,

I have a check box control on my form and would like to create an expression:

In the form I have 3 bound controls( Founding Source, EndDate, DropDate)

In my check box control I would like to see a check mark whenever the
funding source = 1, and EndDate is earlier than today, and the DropDate is
empty or no dropdate. I have the following expression:

=IIf([tstudentclass subform1].[Form]![FundingSource]=1,"YES" And
IIf([tstudentclass subform1].[Form]![EndDate]<=(Now()),"YES","NO" And
IIf([tstudentclass subform1].[Form]![DropDate]=null,"YES"))).

For some reason, the fundingsource works but not the other two??? Any idea
why?? OR a better solution would be great.

John

 

[Ken Sheridan]

Its all down to Boolean logic, which my dictionary defines as: Of or relating
to a combinatorial system devised by George Boole that combines propositions
with the logical operators AND and OR and IF THEN and EXCEPT and NOT.

Any expression which compares one value to another will evaluate to TRUE or
FALSE, so,

[tstudentclass subform1].[Form]![FundingSource] = 1

will be TRUE if the value of the control is 1, FALSE if its something else.
By combining several expressions with a Boolean AND the combined expression
will evaluate to TRUE if all the sub-expressions evaluate to TRUE, but if one
or more evaluate to FALSE then the whole expression will evaluate to FALSE.

One potential fly in the ointment is NULL. If you compare any value with
NULL the result won't be FALSE, but NULL. Even NULL = NULL evaluates to
NULL. This is because NULL is not a value, but an absence of a value, an
'unknown', so the result must also be 'unknown', i.e. NULL. Don't confuse
NULL with zero when dealing with numbers. 5 + 0 = 5, but 5 + NULL = NULL.
In the jargon we say that 'Nulls propagate'. Sometimes this behaviour of
Nulls can be usefully exploited, but that's another story.

 

[John]

Thank you so much Ken.

Is there a good reference book you'd recommend that deals with logical
functions?

Its all down to Boolean logic, which my dictionary defines as: Of or relating
to a combinatorial system devised by George Boole that combines propositions
with the logical operators AND and OR and IF THEN and EXCEPT and NOT.

Any expression which compares one value to another will evaluate to TRUE or
FALSE, so,

[tstudentclass subform1].[Form]![FundingSource] = 1

will be TRUE if the value of the control is 1, FALSE if its something else.
By combining several expressions with a Boolean AND the combined expression
will evaluate to TRUE if all the sub-expressions evaluate to TRUE, but if one
or more evaluate to FALSE then the whole expression will evaluate to FALSE.

One potential fly in the ointment is NULL. If you compare any value with
NULL the result won't be FALSE, but NULL. Even NULL = NULL evaluates to
NULL. This is because NULL is not a value, but an absence of a value, an
'unknown', so the result must also be 'unknown', i.e. NULL. Don't confuse
NULL with zero when dealing with numbers. 5 + 0 = 5, but 5 + NULL = NULL.
In the jargon we say that 'Nulls propagate'. Sometimes this behaviour of
Nulls can be usefully exploited, but that's another story.

Thank you Very much Ken.

I guess I didn't need to use the IIf function? How does it now that it
should be true?

 

[Ken Sheridan]

There are a large number of books on Boolean logic but they are usually
directed at mathematicians or electronics engineers (Boolean logic is used in
the design of electronic circuits). I suspect you would not find them
appropriate in this context. You can, however, find an outline of basic
Boolean logic at the following site:

http://www.ithaca.edu/library/course/expert.html

A Google search for 'Boolean Logic' will throw up a number of similar sites,
along with more advanced ones. As you'll see the concepts are actually very
simple. The main thing which confuses people is that we use AND and OR
rather differently in plain English sometimes. We might say in plain English
"Who are my customers in London and Paris?". In Boolean logic, however,
you'd use the OR operator not the AND operator, because this question should
really be phrased as "Who are my customers based in London or in Paris or in
both?". The question as originally phrased would strictly speaking refer to
customers who have bases in both cites, not either, although the latter is
usually what we would interpret such a question as. In a query we'd put:

SELECT Customer
FROM Customers
WHERE City = 'London' OR City = 'Paris';

Generally speaking you'd use OR when both propositions refer to one column
in the table, AND when they refer to separate columns, so you might use:

SELECT Customer
FROM Customers
WHERE City = 'London' AND CreditLimit >= 10000;

This would return customers in London who have a credit limit of 10,000 GBP
or above.

If you did want to return customers with bases in Paris and London then
you'd need more than one table, as the multiple customer addresses would be
in a separate Addresses table related many-to-one to Customers, so the query
would lok like this:

SELECT DISTINCT Customer
FROM Customers INNER JOIN Addresses
ON Customers.CustomerID = Addresses.CustomerID
WHERE City = 'London' OR City = 'Paris';

Note that we are using the OR operator here still as we are looking for
values in the same column, the City column of the Addresses table. There is
another operator XOR (eXclusive OR). This is not often used, but in this
case could be used to find customers in Paris or London, but not in both:

SELECT DISTINCT Customer
FROM Customers INNER JOIN Addresses

 

[John]

Thank you.

There are a large number of books on Boolean logic but they are usually
directed at mathematicians or electronics engineers (Boolean logic is used in
the design of electronic circuits). I suspect you would not find them
appropriate in this context. You can, however, find an outline of basic
Boolean logic at the following site:

http://www.ithaca.edu/library/course/expert.html

A Google search for 'Boolean Logic' will throw up a number of similar sites,
along with more advanced ones. As you'll see the concepts are actually very
simple. The main thing which confuses people is that we use AND and OR
rather differently in plain English sometimes. We might say in plain English
"Who are my customers in London and Paris?". In Boolean logic, however,
you'd use the OR operator not the AND operator, because this question should
really be phrased as "Who are my customers based in London or in Paris or in
both?". The question as originally phrased would strictly speaking refer to
customers who have bases in both cites, not either, although the latter is
usually what we would interpret such a question as. In a query we'd put:

SELECT Customer
FROM Customers
WHERE City = 'London' OR City = 'Paris';

Generally speaking you'd use OR when both propositions refer to one column
in the table, AND when they refer to separate columns, so you might use:

SELECT Customer
FROM Customers
WHERE City = 'London' AND CreditLimit >= 10000;

This would return customers in London who have a credit limit of 10,000 GBP
or above.

If you did want to return customers with bases in Paris and London then
you'd need more than one table, as the multiple customer addresses would be
in a separate Addresses table related many-to-one to Customers, so the query
would lok like this:

SELECT DISTINCT Customer
FROM Customers INNER JOIN Addresses
ON Customers.CustomerID = Addresses.CustomerID
WHERE City = 'London' OR City = 'Paris';

Note that we are using the OR operator here still as we are looking for
values in the same column, the City column of the Addresses table. There is
another operator XOR (eXclusive OR). This is not often used, but in this
case could be used to find customers in Paris or London, but not in both:

SELECT DISTINCT Customer
FROM Customers INNER JOIN Addresses