Improve company productivity with a Business Account.Sign Up

x
?
Solved

Invalid Use of Null after editing query SQL

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

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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

607 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