#Error showing up in multiple LEFT JOIN statement Access query when value should be NULL

Question

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 the table, LEFT JOIN with Year-1, Year-2, and Year-3 (to get 4 years of data) and then return Val for each year. Here's the SQL:

SELECT a.ID, a.year AS [Year], a.Val AS VAL, 
  a1.year AS [Year-1], a1.Val AS [VAL-1], 
  a2.year AS [Year-2], a2.Val AS [VAL-2], 
  a3.year AS [Year-3], a3.Val AS [VAL-3]
FROM (
  ([A_TABLE] AS a 
  LEFT JOIN [A_TABLE] AS a1 ON (a.ID = a1.ID) AND (a.year = a1.year+1)) 
  LEFT JOIN [A_TABLE] AS a2 ON (a.ID = a2.ID) AND (a.year = a2.year+2)) 
  LEFT JOIN [A_TABLE] AS a3 ON (a.ID = a3.ID) AND (a.year = a3.year+3)

The problem is that, for past years where there is no data (eg, Year-1), I see "#Error" in the appropriate VAL column (eg, [VAL-1]). The weird thing is, I see the expected "null" in the Year column (eg, [YEAR-1]).

Some sample data:

ID     YEAR  VAL
Dave   2004  1
Dave   2006  2
Dave   2007  3
Dave   2008  5
Dave   2009  0

outputs like this:

ID    YEAR  VAL  YEAR-1  VAL-1  YEAR-2  VAL-2  YEAR-3  VAL-3
Dave  2004  1            #Error         #Error         #Error
Dave  2006  2            #Error 2004    1              #Error
Dave  2007  3    2006    2              #Error 2004    1
Dave  2008  5    2007    3      2006    2              #Error
Dave  2009  0    2008    5      2007    3      2006    2

Does that make sense? Why am I getting the appropriate NULL val for the non-existent YEARs, but an #Error for the non-existent VALs?

(This is Access 2000. Conditional statements like "IIf(a1.val is null, -999, a1.val)" do not seem to do anything.)

EDIT: It turns out that the errors are somehow caused by the fact that A_TABLE is actually a query. When I put all the data into an actual table and run the same query, everything shows up as it should. Thanks for the help, everyone.

Answer 1

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.

Answer 2

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

Answer 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.