By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

invalid use of null

P: 15
I have the following line of code that is giving me "Invalid use of null" when it executes.

TotUsage = DSum("[WAT12]", "[T_USAGE]", "[ACCNT] = " & ACCNT & " AND not isnull([MeterExt]) and [Active] = 'Y' and [LoadRoute] = 'Y'")

WAT12 has numeric data in it. The problem has to be with isnull([MeterExt]) becasue it DOES contain a null, but I need to make sure I only select accounts where Active=Y, LoadRoute=Y and MeterExt is not null. Any suggestions on how this should be coded?

Thanks
Sep 5 '07 #1
Share this Question
Share on Google+
3 Replies


JConsulting
Expert 100+
P: 603
I have the following line of code that is giving me "Invalid use of null" when it executes.

TotUsage = DSum("[WAT12]", "[T_USAGE]", "[ACCNT] = " & ACCNT & " AND not isnull([MeterExt]) and [Active] = 'Y' and [LoadRoute] = 'Y'")

WAT12 has numeric data in it. The problem has to be with isnull([MeterExt]) becasue it DOES contain a null, but I need to make sure I only select accounts where Active=Y, LoadRoute=Y and MeterExt is not null. Any suggestions on how this should be coded?

Thanks
Give this a try...assumes that your Y fields are boolean?
Expand|Select|Wrap|Line Numbers
  1. TotUsage = DSum("[WAT12]", "[T_USAGE]", "[ACCNT] = " & ACCNT & " AND NZ([MeterExt],0) <>0 and [Active] = -1 and [LoadRoute] = -1")
  2.  
  3.  
Sep 6 '07 #2

P: 35
There is a function in VB nz(variable,replacement) which you can use to change any null fields to whatever you want them to be.

ie nz(string,"") turns a null into a blank string, but this can also be used for numbers I've found, nz(number,0)

It may be that you are trying to add a null to a number and that's what the problem is.
Sep 6 '07 #3

P: 15
That solved it. Thanks, I didn't know about that command. Will come in handy in the future.
Sep 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.