Type mismatch with year() function

[nharrison]

I have a table called Inflation Rates and one called Project Info.

Project Info has the field "Date Completed", and Inflation Rates has "Date Year" and "Cost Index".

My first query is called "Project List Query" which has the fields "Project Info.*" as well as the calculated fields:

Year Completed: Nz(Year([Actual Completion]),"1900")
Current Year: Year(Now())

The second query is called "Project Inflation", which has the following SQL source:

SELECT [Adj Contract Amount]/[Size (sq ft)] AS [Adj Cost/SF], IIf([Inflation Rates].[Cost Index]=0,[Contract Amount],[Contract Amount]*([Inflation Rates_1].[Cost Index]/[Inflation Rates].[Cost Index])) AS [Adj Contract Amount]
FROM ([Project List Query] LEFT JOIN [Inflation Rates] ON [Project List Query].[Year Completed] = [Inflation Rates].[Date Year]) LEFT JOIN [Inflation Rates] AS [Inflation Rates_1] ON [Project List Query].[Current Year] = [Inflation Rates_1].[Date Year];

The bolded section represents the joins for the query, and the unbolded part is my two calculated fields, the only two in the query:

Adj Cost/SF: [Adj Contract Amount]/[Size (sq ft)]
Adj Contract Amount: IIf([Inflation Rates].[Cost Index]=0,[Contract Amount],[Contract Amount]*([Inflation Rates_1].[Cost Index]/[Inflation Rates].[Cost Index]))

My issue is that when I run the query "Project Inflation", I receive the error "Type mismatch in expression." Normally this is an error of data types, but I can figure out where. I have tried removing the calculated fields, but still receive the error - which leads me to believe that the error exists in the joins.

With the joins, there is two relationships: "Project List Query" --- "Inflation Rates", between "Year Completed" and "Date Year", respectively. Also, "Project List Query" --- "Inflation Rates_1", between "Current Year" and "Date Year", respectively.

The data type of "Date Year" is long integer, and I have tried changing it to short integer as well. According to an access reference, http://www.webcheatsheet.com/SQL/access_functions/year.php
the data type of the Year() function is an integer as well.


So where is my "type mismatch"?

 

[ByteMyzer]

In the Query Project List Query, for expression Year Completed, try changing:

Nz(Year([Actual Completion]),"1900")

...to:

CLng(Nz(Year([Actual Completion]),1900))

 

[Brianwarnock]

The use of Nz creates a text value try
Year Completed: Cint(Nz(Year([Actual Completion]),"1900"))

Brian

oops, really must learn to type quicker

 

[nharrison]

Worked perfect. Thanks both for suffering through my long explanation

and @Brian, I think in this case it was learning to read faster haha. I had quite the essay