Problems assigning a NULL value to a variable

[dgillz]

In excel VBA, I have declared a variable:

Dim stAstDesc2 as String

Later, I try to assign this variable a NULL value which is the result of a SQL query of a table:

Select ast_desc_2 from fxastfil_sql..
stAstDesc2 = !ast_desc_2

The SQL is executed, and I have several dozen variables that are successfully being assigned a value just like the example above, however this variable and about 6 others all fail because the retrieved value from the SQL table is null. I am getting "Error 94 - Invalid Use of Null" errors.

Why is this and what can I do to avoid this?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

stAstDesc2 = !ast_desc_2 [blue]& ""[/blue]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[genomon]

Or Nz(!ast_desc_2, "") ??

Beir bua agus beannacht!

 

[PHV]

Or:
Dim stAstDesc2 As Variant

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

The real problem here is that NULL is a lack of information. How it is implemented, and how it is presented, depends on the DBMS that you use. How you choose to deal with it is a decision that you must make, and implement.

Although you may choose to equate it with a zero length string, it is not one, nor any other sort of string and, strictly, needs special handling. The various ideas proposed are all reasonable choices, but there is no one-size-fits-all solution that you can make, and you must make your own reasonable choice based on what you know about your data.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Andrzejek]

I agree with Tony.

So in essence you have this:

If IsNull(!ast_desc_2) then
    stAstDesc2 = "Unknown"
Else
    stAstDesc2 = !ast_desc_2
End If

I hope we will hear back from dgillz ...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dgillz]

I tried the IsNull() test and it got the same error. I used the solution provided by Andrzejek

stAstDesc2 = !ast_desc_2 & ""

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html