"Invalid use Of Null" In Recordset Containing a Memo Field set to Null

[grahamers]

OK this is a strange one.

In Access 2010 32 bit, I am attempting to open a recordset based on a query and read certain fields in so that I can update a different table.

While the query contains many fields, the fields I am attempting to manipulate are an ID ("ID") field and a Memo field ("[Description Note]").

When I read any field from the recordset for a record that contains an empty [Description Note] field, I get an "Invalid use Of Null" error.

Here is my code:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSql As String
    Dim sNote As String
    Dim iPMID as Integer


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryPMs", dbOpenSnapshot)
    Do While Not rst.EOF
        iPMID = rst![ID]                    <------------------------ Code breaks here.
        sNote = rst![Description Note]
    {DO STUFF}        
    rst.MoveNext
    Loop

if every [Description Note] field has at least one character in it, the code works great. however, if any [Description Note] is empty, then the code breaks as soon as I read any field from that record.

I have tried changing the type of DAO recordset I am opening and I have tried re-ordering which fields I read first. All to no avail.

This has to be a common issue, but extensive Googling has turned up nothing.

Also, I don't know if this matters, but one of the tables in the query I am reading is a linked SharePoint table that contains both fo the fields in question.

Any thoughts?

Thanks for any tips in advance!

-Graham

[lfpm062010]

Is "iPMID" defined as string or variant?

String will not accept "Null" but "Variant" will.

[grahamers]

It is an integer, but the code breaks if I read Description Note first instead of iPMID.

[grahamers]

Is "iPMID" defined as string or variant?

String will not accept "Null" but "Variant" will.

You are a life saver!

This fixed it. I changed PMID from Integer to variant and it worked. The odd part was that the code was throwing an error when reading iPMID which was NOT null BEFORE I tried to read the Description Note which was null.