Solved

Invalid Use of Null after editing query SQL

Posted on 2011-03-15
7
437 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:SpaceManagers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 35136732
Could be due to formatting of the SQL in the SQL View editor. If that editor splits a line at the wrong place, then Access could misinterpret that as a NULL value (or a malformed SQL, or any number of things). Generally Access will highlight the troublesome area in the SQL Viewer - can you determine where this is occurring?
0
 
LVL 1

Author Comment

by:SpaceManagers
ID: 35136939
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
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 166 total points
ID: 35137573
post the sql statement..

are you using subqueries?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:SpaceManagers
ID: 35137706
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
0
 
LVL 1

Author Comment

by:SpaceManagers
ID: 35352195
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
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 167 total points
ID: 36116116
Using the bang as separator is not standard. The query analyser might mistake it for an object reference. The standard SQL notation would be:

... Not Like qrySpaces_RAD.rm_std
It's also unusual to apply the Like operator between two fields, and even more when the tables are already joined. Could you explain the purpose?

I have no idea if that is related to your problem, as I never had the “Invalid use of Null” error from the Jet Engine, and I'm not sure it even exists. It is however a standard error message for Visual Basic, and probably also for Visual Basic function calls from within a query. Do your queries use function calls?

(°v°)
0
 
LVL 1

Author Closing Comment

by:SpaceManagers
ID: 36207082
Problem is not resolved.
Divided points amongst experts who tried to help.
Thanks for your efforts
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question