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

M

Mr ED

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

Mr ED

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

Mr ED

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

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

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

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

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