Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Invalid Use of Null after editing query SQL

Posted on 2011-03-15
7
Medium Priority
?
448 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
7 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 334 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 332 total points
ID: 35137573
post the sql statement..

are you using subqueries?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 334 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

885 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