Working with Null values in a recordset

[Jimmy Turnip]

It's OK! I've solved my own problem by using the IsNull() function instead of If Object Is Null ...

I love VBA. I love VBA. I love VBA. I love...

----------------------------------------------------

I have code similar to this in my program:

With MyRecordset
If .RecordCount > 0 Then _
** MyFunction = IIf(.Fields!MyTotal Is Null, 0, .Fields!MyTotal)
End With

MyRecordset is based on an SQL statement similar to this:

SELECT Sum(MyTable1.MyField) AS MyTotal
FROM MyTable1 INNER JOIN MyTable2 ON blah blah blah
WHERE {includes criteria on both tables}

If there are no records matching the criteria following the WHERE clause then the value returned is Null. When I run the program I get run-time error 'object required' when it hits **.

I keep coming back to this bug, but I just can't see how to solve it. I've checked for spelling errors, etc., and the program definately recognises MyRecordset because you can see it in the Watch window.

Ahhhh! Any help would be greatly appreciated.

Thanks.

[This message has been edited by Jimmy Turnip (edited 10-25-2000).]

 

[chrismcbride]

Jimmy T
I am not sure what exactly is going on with this code. I could not see anything wrong with it so I made a copy on my own machine and everything ran fine. The only difference was that I did not use ** anywhere as I assume you put that there to highlight the problem line in your code. The other difference was that I used the syntax...

IIF(IsNull(.Fields!MyTotal...

I even set up a loop to go through null values and non null values to see if there was a problem, and there wasn't. I also assumed that you are assigning the value of the IIF line inside MyFunction.
What do you think???

P.S. send me an e-mail if I don't see a follow up posting.
Chris

 

[Jimmy Turnip]

Thanks for your reply, Chris. The problem was solved when I used...

IIf(IsNull([FieldName]),...)

...instead of...

IIf([FieldName] Is Null,...)

I don't understand why, but the first one works but the second one returns an error.

Incidentally, I got this tip by searching old discussion topics. I guess the lesson here is to find out if your problem has been experienced by anyone else, before you waste the time of helpful people such as yourself!

Thanks a lot,

JT