Access Query Error - Null & Variant Data Types - How do I fix this error?

Question

All,

This error is driving me insane. I've spent 2 hours trying to figure it out and/or work around it with no luck. Here's the error:

"You tried to assign the NULL value to a variable that is not a Variant data type."

Here's my SQL:

SELECT tbl_budir_002.Location_Index, tbl_parent_001.NEWPARENTID 
INTO tbl_budir_003
FROM (tbl_budir_002 
    LEFT JOIN qry_parent_003 
        ON tbl_budir_002.Location_Index = qry_parent_003.Location_Index) 
    LEFT JOIN tbl_parent_001 
        ON qry_parent_003.PARENTNAME = tbl_parent_001.PARENTNAME;

I think the complication comes in at this point -- tbl_parent_001 was created via group by from another larger table (to singulate the PARENTNAME records). I had to manually go in to the table and assign the field NEWPARENTID as type autonumber so that I could give the PARENTNAME records primary keys. Now, when I run the query, I get the above error. I suspect it's the autonumber that's causing me issues, but I don't know any other way to create a primary key through using a query.

So, I need help on two things: (1) alleviating the error given my current query setup and (2) figuring out a better way to create a primary key rather than entering it in manually via the table's design view.

Thanks, Noah

Solution 1

Here's what's going on: http://support.microsoft.com/kb/197587. Besides Access 2000, the information in the KB at least applies to Access 2002 from my experience. Beyond workaround methods 1 and 2, if your solution can stand it, I was successful wrapping my autonumber field in NZ().

Solution 2

I got the error message last week and tried several things to fix this. In my case, this had to do with me trying to append data to a table but not realizing that I was appending to the primary key which was an auto number field (accidentally of course).

Once I removed this field from my append query everything worked as intended.