Solved

Invalid Use of Null after editing query SQL

Posted on 2011-03-15
7
427 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 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 119

Assisted Solution

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

are you using subqueries?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 43
Export  Access Query To Excell 16 47
SQL profiler equivalent in MS-Access 3 45
subform is not filtered by link field 8 21
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now