#error Showing Up In Multiple Left Join Statement Access Query When Value Should Be Null

I'm trying to return an ID's last 4 years of data, if existing. The table (call it A_TABLE) looks like this: ID, Year, Val The idea behind the query is this: for each ID/Year in th

Solution 1:

I think that the fact that a_table is a query may matter (see my comment on your post).

Solution 2:

What version of Access are you using? It could be another odd quirk/bug of Access. Is the Val column a non-null? Maybe Access is seeing that and making the Val columns in your resultsets non-null, which creates an error when they turn out to be null.

Maybe you just want to deal with it with a conditional statement to hide the error:

SELECT a.ID, a.year AS [Year], iif(isnull(a.year),0,a.Val) AS VAL...

I think isnull is the right syntax for Access, but if it's not, you get the idea.

Solution 3:

I had the same problem, except I was doing a very basic single left join between a table and a query. The field I was joining on was a Long Integer in the table, but it was converted using CLng() in the original query. The left join gave me the #Error problem. Changing the column in the table to Text and removing the CLng() conversion solved my problem. I don't know enough about MS Access data formats and conversions to know why this was a problem. I'm using Access 2003.