Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Invalid use of null value (Read 3458 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Invalid use of null value
03.08.11 at 18:14:50
 
Invalid use of null value
 
Quote:
I got an error. I can't remember the exact text in the dialog but it was something like "Invalid use of null value." It happened when I was assigning a query value to a variable and the value returned by the query for that particular field happened to be null. I added some text there in the database, and that error went away. Here was the statement that failed:

longDesc = CStr(outer1.fields("ABC_EXP").Value)

ABC_EXP happened to be null.

 
This is not a problem in SQL*XL. It is a general programming 'challenge'. The value of a field is of type variant. That can contain any datatype, even objects or collections. And they can contain null values.
 
Assumed your column ABC_EXP is of type number, you can indeed use the CStr function to convert its value to a string. However ... when column ABC_EXP is left blank in the database (null) the CStr function will fall over with a message like:  Invalid use of null value.
 
The way around this is to use a test. Test if the value is a null. If so, assign your string variable to an empty string and otherwise convert the value of your column:
 
if isNull(outer1.fields("ABC_EXP").Value) then
   longDesc = vbNullString
else
   longDesc = CStr(outer1.fields("ABC_EXP").Value)
end if
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1