asked on

Invalid Use of Null after editing query SQL

Whenever I resort to editing or even just viewing a query in SQL I can no longer open the query. I will get an error message 'Invalid Use of Null'. There are no null values in the dataset. The query will work OK until such time as I look at the SQL. Once I have done that I cannot open the query in any way, not even in Design View. It will always return the same error. Yet I can export the query and see the recordset returned in, for example, Excel.
What can be causing this problem?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SpaceManagers

ASKER

I have captured the sql by importing the query into another empty database. I then mirrored the sql in a new view in the original database. I was able to see the recordset in the new view and return to the Design View. I then looked at the SQL, copied the SQL , created a new query and inserted the SQL. This latest query will show the recordset but when I try to see it in Design View it will give me the Invalid Use of NULL error and when I click OK it returns me to the sql. I can still see the recordset but not the design view.
save the queries and close the database.
Reopen the database and now the newly created query, whence I copied the SQL, no longer works.I get the error message Invalid use of Null. This is the query that i created from scratch, mirroring the sql of the earlier query. I then viewed and copied, without changing in any way, the SQL. Now after closign the db it fails.
I have tried to repair the database but it has no effect

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SpaceManagers

ASKER

Yes, it does use subqueries and a quick check on a single table works without fault, ie I cannot replicate the problem

SELECT qrySpaces_GSD.SpaceCode, qrySpaces_GSD.SpaceStandardCode, qrySpaces_RAD.rm_std, qrySpaces_GSD.SpaceStandardName, qrySpaces_RAD.RAD_rmstd.description
FROM qrySpaces_GSD INNER JOIN qrySpaces_RAD ON qrySpaces_GSD.SpaceCode=qrySpaces_RAD.rm_id
WHERE (((qrySpaces_GSD.SpaceStandardCode) Not Like [qrySpaces_RAD]![rm_std]));

This SQL is Access generated from a qbe window

SpaceManagers

ASKER

I have just tried to Import spreadsheet data from Excel using the Access wizard.
When I have completed all the tasks and then click the Finish button I get the Invalid use of Null error message

SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SpaceManagers

ASKER

Problem is not resolved.
Divided points amongst experts who tried to help.
Thanks for your efforts