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

Answer 1

It looks like your problem may be the LEFT JOIN. Since you are inserting into a table that does not exist yet access may be trying to set the NEWPARENTID column as a primary key. try running the query as JOIN to see if you get the same error. You may also want to try creating the table manually first then inserting so Access doesn't have to guess what you want.

Answer 2

OK, you will have to account for nulls in your database. It's not hard, but can be annoying sometimes. You cannot implicity convert a null to text like you can a number. Use nz - Null To Zero. If the replacement is text, use "", if number like below, use 0. for example nz(myText,"") or nz(myNumber,0)

SELECT nz(tbl_budir_002.Location_Index,0), 
       nz(tbl_parent_001.NEWPARENTID ,0)...

You can also use IsNull() but it's not as handy as you would have to use a conditional statement to set it to something else. I use isnull on criteria and nz on everything else.

iif(isnull(tbl_budir_002.Location_Index),0,tbl_budir_002.Location_Index)

Have fun.