Mismatch Error Excel Link to Access

[rpresas]

I added a pmt expression to a query. This query is linked to Access through excel as a pivot table. When I tried to update my pivot table, I get a mismatch expression error. I verified the values the expression is referring to are indeed values. I scrolled through my data and could not find any non values. The field value is rounded to 2 decimals. Any ideas on what is causing this? Below is the SQL view. I have tried adding a ISNULL and ISNUMERIC constraint with no success. Not sure what to do.

SELECT [Loan Number], SSN, [Loan Date], [Loan Amount], Payments, APR, [Current Loan Date], [Store ID], [Store Number], [Current Balance], [Due Date2], [Last Pay Date], [Last Pay Amount], Region, [True Last Payment], [# Days Since Last Payment], [Interest Accrual for Period], [Total Accrued Interest], [Portfolio Balance], [Field Status], [CFO Status], DateSerial(Year([Loan Date]),Month([Loan Date])+1,0) AS Pool, [CRS Date] AS [Checkpro Write Off Date], [WRT OFF $], [CCFI Funded], [Month End], [Write Off Month End], [Confirmed Type-Checkpro] AS Type, [SSN] & Format([Loan Number],"00000") AS [Format], IIf([CFO Status]="PIF",[Loan Amount],0) AS [Paid In Full], Pmt([APR]/100/12,[Payments],[Loan Amount],0,0) AS Contract_Payment
FROM [Loan Master Data];

 

[strive4peace]

even though everything seems to have a value, I have seen Access complain when references are not wrapped with NZ and they are surrounded with functions that require a value

DateSerial(Year(nz([Loan Date])),Month(nz([Loan Date]))+1,0) AS Pool

Format(nz([Loan Number]),"00000") as [LoanFormat] -- even though you have brackets, Format is a reserved word and I would refrain from using it for a name

IIf(nz([CFO Status],"")="PIF",[Loan Amount],0) AS [Paid In Full]

Pmt(nz([APR],0)/100/12,[Payments],[Loan Amount],0,0) AS Contract_Payment -- is Payments a valid field? seems like it might be more than one value ... just guessing

What happens when you paste this SQL statement into the SQL view of a query and run it?

 

[rpresas]

even though everything seems to have a value, I have seen Access complain when references are not wrapped with NZ and they are surrounded with functions that require a value

DateSerial(Year(nz([Loan Date])),Month(nz([Loan Date]))+1,0) AS Pool

Format(nz([Loan Number]),"00000") as [LoanFormat] -- even though you have brackets, Format is a reserved word and I would refrain from using it for a name

IIf(nz([CFO Status],"")="PIF",[Loan Amount],0) AS [Paid In Full]

Pmt(nz([APR],0)/100/12,[Payments],[Loan Amount],0,0) AS Contract_Payment -- is Payments a valid field? seems like it might be more than one value ... just guessing

What happens when you paste this SQL statement into the SQL view of a query and run it?

I ran an update query against the original table, creating a new column with the formula and I discovered there was 4 rows (out of 404k) with missing APR's which caused an error in the formula. I thought I had effectively built a fail safe in the query with a Iif formula but it didn't take. I might just do a direct update to the table. Frustrating three hours!

 

[strive4peace]

glad you figured it out! thanks for sharing ~