Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Invalid Use of Null after editing query SQL

Posted on 2011-03-15
7
Medium Priority
?
443 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 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
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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 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