Receiving "Invalid use of Nulls" in running Access application, h.

[Guest]

Getting an error box when running an Access application. How do I find where
the problem is located. I cannot seem to add breaks or track flow of program
execution.

 

[Allen Browne]

Here's a list of the 6 most common mistakes people make with Null. It may
help you to understand what you have done to cause the error:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html

 

[Guest]

I am looking at hundreds of lines of someones code and no idea what is
causing the error box. How do I let the system tell me where the error is
located?

 

[Allen Browne]

Sorry: I can't debug (or even see) the code for you from here.

 

[Guest]

I understand, what I am asking is how to break into the execution and insert
Breaks or something to let the system tell me where the problem is located.
When I click on the GO button, I want to watch the execution or have the
program stop on the offending code.

 

[Allen Browne]

From any code window, choose Options on the Tools menu.
On the General tab, make sure Error Trapping is set to "All Errors" or
"Unhandled Errors".

In any code that has error handling, disable it. One way to do that is to do
a global replace for:
On Error Goto
with:
'On Error Goto

Of course, this assumes we are talking about errors caused by code. Some
errors come from the engine.

You also need to verify that the code actually compiles: Compile on Debug
menu (from code window).

 

[onedaywhen]

Here's a list of the 6 most common mistakes people make with Null. It may
help you to understand what you have done to cause the error:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html

Interesting article. May I suggest an addendum to you Error 1 ('Nulls
in Criteria') description?

Using the Customers table from Northwind, here's a summary of what you
have so far:

SELECT DISTINCT
(
SELECT COUNT(*) FROM Customers) AS Total,
(
SELECT COUNT(*) FROM Customers WHERE Region = 'BC') AS is_BC,
(
SELECT COUNT(*) AS Counts FROM Customers WHERE Region <> 'BC') AS
not_BC,
(
SELECT COUNT(*) AS Counts FROM Customers WHERE Region IS NULL) AS
is_null FROM customers
;

Consider this in isolation:

SELECT COUNT(*) FROM customers WHERE Region <> 'BC';

My Northwind is unaltered so the query returns the value 29. Let's use
this in an arbitrary CHECK constraint on another table:

ALTER TABLE Products
ADD CONSTRAINT ch__DopMe1
CHECK (50 < (
SELECT COUNT(*) FROM customers WHERE Region <> 'BC')
)
;

Now let's try to add a new row into the products table:

INSERT INTO Products (ProductName, Discontinued)
VALUES ('DeleteMe1',0)
;

This INSERT succeeds whereas your description says it should fail. To
demonstrate the point, let's apply a second CHECK constraint, replacing
the query with the literal value of 29:

ALTER TABLE Products
ADD CONSTRAINT ch__DopMe2
CHECK (50 < 29)
;

And try a second INSERT:

INSERT INTO Products (ProductName, Discontinued)
VALUES ('DeleteMe2',0)
;

This time, the second CHECK constraint bites and the INSERT fails. Why
not the first?

The answer is that your description of 'Nulls in Criteria' only applies
only to SQL DML (Data Modification Language e.g. SELECT syntax).
Unknown values are treated differently in SQL DDL (Data Definition
Language e.g. ALTER TABLE). In a DDL WHERE clause, the unknown result
of an expression does not exclude the row from the results set.

I am aware that this discussion goes beyond the needs of the 'Casual
User'; most 'Power' users seem unaware of the existence of the CHECK
constraint syntax. However, I think the fact that 'nulls in criteria'
is handled differently for DDL is due at least a mention.

Jamie.

--

 

[Allen Browne]

Hi Jamie

Thanks for posting this. I had not struck this inconsistency in handling
nulls between DML and DDL, but I was able to demonstrate it following your
steps.

Is this pecular to JET, or is it also in SQL Server?

Is the inconsistency part of the ANSI standard, or one of those gray areas?
(I can't imagine any good reason for the inconsistency, but wondering if
there is a reference I should be aware of.)

 

[onedaywhen]

Is this pecular to JET, or is it also in SQL Server?

Is the inconsistency part of the ANSI standard, or one of those gray areas?
(I can't imagine any good reason for the inconsistency, but wondering if
there is a reference I should be aware of.)

Allen, It is in both the ANSI standard and SQL Server by design. I
think the principle involved is 'the benefit of the doubt' i.e. if the
expression results in unknown, the CHECK constraint doesn't know for
sure to reject, so lets it pass.

Jamie.

--

 

[Allen Browne]

Thanks, Jamie.

Have added a small note to the article to alert people who know what a DDL
query is about the different behavior, hopefully without confusing the
casual user too much.

 

[onedaywhen]

Thanks, Jamie.

Have added a small note to the article to alert people who know what a DDL
query is about the different behavior, hopefully without confusing the
casual user too much.

Here's use a better example than my earlier arbitrary one:

CREATE TABLE EarningsHistory (
employee_id INTEGER NOT NULL,
earnings CURRENCY NOT NULL,
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
PRIMARY KEY (employee_id, start_date),
CHECK (start_date < end_date)
) ;

The row with the NULL end_date for a given employee_id represents the
current salary for an employee. The CHECK constraint in the above is
the bear minimum to help prevent bad data i.e. the start_date must
occur before the end_date. I trust you can see how the DDL must allow a
NULL end_date to pass for the CHECK constraint to of any use.

Jamie.

--

 

[Allen Browne]

Thanks, Jamie.

I can follow the logic of what you are saying, but from where I sit it is a
blatent inconsistency in the SQL language. Of course, most human and
computer languages have these kinds of inconsistencies, so we live with it.
Hopefullly the inconsistency is applied consistently in all implementations
of the language.

Your example actually highlights an inconsistency with Access.

In Access 97, create a table with fields StartDate and EndDate.
Then set the Validation Rule of the table (in the Properties box, not the
lower pane of table design) to:
[StartDate]<[EndDate]
The rule is NOT satisfied if either field is Null.

In Access 2000, the same rule IS satisfied, regardless of the Nulls.
Perhaps Microsoft changed JET 4 to comply with the DDL constraint.