Ask a new question

Don't Understand 'Invalid Use of Null' Error Message

Hi:

I'm linking two tables that are both part of a company ODBC database with Access 2007.  The query runs and displays data, but as I begin to scroll through the data, it returns the error message 'Invalid Use of Null' and all the fields change to ?Name.  I don't understand the context of the message.  The data I'm linking (called REP_NUM) is text in one table and a number in the other, so I used the function CSTR to convert the number to text in a query, then linked that new field in the query to the corresponding text field in the other table.  My problem starts when I link the two tables, and as stated, it runs, but i can't scroll through all the data. 

Can you help me understand the context or how to correct? I've read some of the messages about this message, but I can't understand how they apply to my situation.  I cannot change the data tables (corporate table), I can only try to fix the queries. 

Thanks in advance for any help!

Karen

Answer
Answer

The problem sounds like one of the columns allows null values (or they both do). While cstr() function will convert the number to a string, the function will error out if the column has a null value (cstr function does not work on null columns).

 

So, you have to use a expression that converts the empty null to some string

 

Try something like:

 

Cstr(nz([column name],""))

  

This type of join will run quite slow. I would perhaps ask the server guy if a view of the joined tables can be created for you. (you then just link to tha view). You would then link to that view and it would eliminate the need for the ctstr() in those joins, and in fact you would not need to create the join either. I suggest this since your current setup will not perform well since those types of joins can't be optimized by access.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer
On Fri, 7 May 2010 03:56:25 +0000, Dangrgrl wrote:
 
I agree with my colleagues, but I take a different tack: should we
have a REP_NUM for all records? If yes, and there currently are nulls,
then that points to the field not being required. You say you have no
control over that, but it may be worth working this issue up the chain
of command. If the chain is rational, they would agree, fix up the
current bad data, and you don't have to go out of your way to deal
with NULL anymore.
 
One can hope, right?
 
-Tom.
Microsoft Access MVP
 
 
>
>
>Hi:
>
>I'm linking two tables that are both part of a company ODBC database with Access 2007. The query runs and displays data, but as I begin to scroll through the data, it returns the error message 'Invalid Use of Null' and all the fields change to ?Name. I don't understand the context of the message. The data I'm linking (called REP_NUM) is text in one table and a number in the other, so I used the function CSTR to convert the number to text in a query, then linked that new field in the query to the corresponding text field in the other table. My problem starts when I link the two tables, and as stated, it runs, but i can't scroll through all the data.
>
>Can you help me understand the context or how to correct? I've read some of the messages about this message, but I can't understand how they apply to my situation. I cannot change the data tables (corporate table), I can only try to fix the queries.
>
>Thanks in advance for any help!
>
>Karen
 
-Tom. Microsoft Access MVP
-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 12, 2023 Views 66,572 Applies to: