Data Type Mismatch in Criteria Expression

G

Guest

I've been working on this query and report for several days now, to no avail.
Any and all suggestions would be extremely grateful for!

First, this query is pulling information from just one table. The main
thing I'm trying to eventually do is to get a sum for each of the years on
the report, but every time I try to do a sum in the Total field, I keep
getting this error. I've also tried using a crosstab query, and get the same
error. I've tried creating another query off of this query to see if I could
pinpoint what it is, but the best I can come up with is it's either not
liking the dates I have in there, or the combined information I have to get
the "Total Payment Forward" - Below is the whole SQL for the 1st and main
query (I wasn't sure if I should just cut and paste the one thing I'm most
concerned about or if the whole picture would be needed to understand the
whole problem) -

SELECT tbl_ARI_Insights_Rental_Downloads.[Month Booked], [In Service],
tbl_ARI_Insights_Rental_Downloads.Client,
tbl_ARI_Insights_Rental_Downloads.Vehicle,
tbl_ARI_Insights_Rental_Downloads.Div,
tbl_ARI_Insights_Rental_Downloads.Yard,
tbl_ARI_Insights_Rental_Downloads.[Bill Client Data],
tbl_ARI_Insights_Rental_Downloads.[Rental Amt],
tbl_ARI_Insights_Rental_Downloads.[Interest Amt],
tbl_ARI_Insights_Rental_Downloads.[Tax Amt],
tbl_ARI_Insights_Rental_Downloads.[Mgmt Fee],
tbl_ARI_Insights_Rental_Downloads.[Ins Fee],
tbl_ARI_Insights_Rental_Downloads.[Total Billed],
tbl_ARI_Insights_Rental_Downloads.[Invoice Number],
tbl_ARI_Insights_Rental_Downloads.[Invoice Date],
tbl_ARI_Insights_Rental_Downloads.[Cap Cost],
tbl_ARI_Insights_Rental_Downloads.[Book Value],
tbl_ARI_Insights_Rental_Downloads.[Mos Bill],
tbl_ARI_Insights_Rental_Downloads.[1st Bill],
tbl_ARI_Insights_Rental_Downloads.[Last Bill],
tbl_ARI_Insights_Rental_Downloads.[CLIENT DATA1],
tbl_ARI_Insights_Rental_Downloads.VIN,
tbl_ARI_Insights_Rental_Downloads.[Lease Term],
tbl_ARI_Insights_Rental_Downloads.[AFE Number], IIf([Lease
Term]=0,0,IIf([Lease Term]=36,[Cap Cost]*0.02778,IIf([Lease Term]=44,[Cap
Cost]*0.0225,IIf([Lease Term]=50,[Cap Cost]*0.0199987,IIf([Lease
Term]=55,[Cap Cost]*0.01819,IIf([Lease Term]=60,[Cap Cost]*0.01667)))))) AS
[Term Payment], [Cap Cost]/1000*0.4 AS [Admin Fee], IIf([Month Booked]<[Last
Pmt Date Long],[Cap Cost]-[Term Payment]*[Mos Bill],0) AS [Curr Cap Cost],
[Curr Cap Cost]*(0.053472737/12) AS [Curr Interest], IIf([Month Booked]<[Last
Pmt Date Long],[Term Payment]+[Admin Fee]+[Curr Interest],0) AS [Total
Payment Forward], DateAdd("m",+1,[In Service]) AS [1st Pmt Date Long],
DateAdd("m",[Lease Term],[1st Pmt Date Long]) AS [Last Pmt Date Long],
Format$([1st Pmt Date Long],"mmm-yyyy",0,0) AS [1st Pmt Date], Format$([Last
Pmt Date Long],"mmm-yyyy",0,0) AS [Last Pmt Date]
FROM tbl_ARI_Insights_Rental_Downloads
GROUP BY tbl_ARI_Insights_Rental_Downloads.[Month Booked],
tbl_ARI_Insights_Rental_Downloads.[In Service],
tbl_ARI_Insights_Rental_Downloads.Client,
tbl_ARI_Insights_Rental_Downloads.Vehicle,
tbl_ARI_Insights_Rental_Downloads.Div,
tbl_ARI_Insights_Rental_Downloads.Yard,
tbl_ARI_Insights_Rental_Downloads.[Bill Client Data],
tbl_ARI_Insights_Rental_Downloads.[Rental Amt],
tbl_ARI_Insights_Rental_Downloads.[Interest Amt],
tbl_ARI_Insights_Rental_Downloads.[Tax Amt],
tbl_ARI_Insights_Rental_Downloads.[Mgmt Fee],
tbl_ARI_Insights_Rental_Downloads.[Ins Fee],
tbl_ARI_Insights_Rental_Downloads.[Total Billed],
tbl_ARI_Insights_Rental_Downloads.[Invoice Number],
tbl_ARI_Insights_Rental_Downloads.[Invoice Date],
tbl_ARI_Insights_Rental_Downloads.[Cap Cost],
tbl_ARI_Insights_Rental_Downloads.[Book Value],
tbl_ARI_Insights_Rental_Downloads.[Mos Bill],
tbl_ARI_Insights_Rental_Downloads.[1st Bill],
tbl_ARI_Insights_Rental_Downloads.[Last Bill],
tbl_ARI_Insights_Rental_Downloads.[CLIENT DATA1],
tbl_ARI_Insights_Rental_Downloads.VIN,
tbl_ARI_Insights_Rental_Downloads.[Lease Term],
tbl_ARI_Insights_Rental_Downloads.[AFE Number];


What do I need to do to allow my query to use the sum function? Or even use
the crosstab function?
 
J

John Spencer

SELECT tbl_ARI_Insights_Rental_Downloads.[Month Booked],
[In Service],
tbl_ARI_Insights_Rental_Downloads.Client,
tbl_ARI_Insights_Rental_Downloads.Vehicle,
tbl_ARI_Insights_Rental_Downloads.Div,
tbl_ARI_Insights_Rental_Downloads.Yard,
tbl_ARI_Insights_Rental_Downloads.[Bill Client Data],
tbl_ARI_Insights_Rental_Downloads.[Rental Amt],
tbl_ARI_Insights_Rental_Downloads.[Interest Amt],
tbl_ARI_Insights_Rental_Downloads.[Tax Amt],
tbl_ARI_Insights_Rental_Downloads.[Mgmt Fee],
tbl_ARI_Insights_Rental_Downloads.[Ins Fee],
tbl_ARI_Insights_Rental_Downloads.[Total Billed],
tbl_ARI_Insights_Rental_Downloads.[Invoice Number],
tbl_ARI_Insights_Rental_Downloads.[Invoice Date],
tbl_ARI_Insights_Rental_Downloads.[Cap Cost],
tbl_ARI_Insights_Rental_Downloads.[Book Value],
tbl_ARI_Insights_Rental_Downloads.[Mos Bill],
tbl_ARI_Insights_Rental_Downloads.[1st Bill],
tbl_ARI_Insights_Rental_Downloads.[Last Bill],
tbl_ARI_Insights_Rental_Downloads.[CLIENT DATA1],
tbl_ARI_Insights_Rental_Downloads.VIN,
tbl_ARI_Insights_Rental_Downloads.[Lease Term],
tbl_ARI_Insights_Rental_Downloads.[AFE Number]

, IIf([Lease Term]=0,0
,IIf([Lease Term]=36,[Cap Cost]*0.02778
,IIf([Lease Term]=44,[Cap Cost]*0.0225
,IIf([Lease Term]=50,[Cap Cost]*0.0199987
,IIf([Lease Term]=55,[Cap Cost]*0.01819
,IIf([Lease Term]=60,[Cap Cost]*0.01667)))))) AS [Term Payment]

, [Cap Cost]/1000*0.4 AS [Admin Fee]

, IIf([Month Booked]<[Last Pmt Date Long],
[Cap Cost]-[Term Payment]*[Mos Bill],0) AS [Curr Cap Cost]

, [Curr Cap Cost]*(0.053472737/12) AS [Curr Interest]

, IIf([Month Booked]<[Last Pmt Date Long],
[Term Payment]+[Admin Fee]+[Curr Interest],0)
AS [Total Payment Forward]

, DateAdd("m",+1,[In Service]) AS [1st Pmt Date Long],
DateAdd("m",[Lease Term],[1st Pmt Date Long]) AS [Last Pmt Date Long],
Format$([1st Pmt Date Long],"mmm-yyyy",0,0) AS [1st Pmt Date],
Format$([Last Pmt Date Long],"mmm-yyyy",0,0) AS [Last Pmt Date]
FROM tbl_ARI_Insights_Rental_Downloads

It may be that you cannot use Term Payment in the subsequent field
and Curr Cap Cost in the subsequent field
and Curr Interest in the subsequent field
and admin fee in the subsequent field.

I would try removing one calculation at a time to see what happens. You
may have to restructure the queries, building one value in a query and
then adding the query into the next level

SELECT [Cap Cost], [Mos bill], [Month Booked]
[Last Payment Date Long]
, IIf([Lease Term]=0,0
,IIf([Lease Term]=36,[Cap Cost]*0.02778
,IIf([Lease Term]=44,[Cap Cost]*0.0225
,IIf([Lease Term]=50,[Cap Cost]*0.0199987
,IIf([Lease Term]=55,[Cap Cost]*0.01819
,IIf([Lease Term]=60,[Cap Cost]*0.01667)))))) AS [Term Payment]
, [Cap Cost]/1000*0.4 AS [Admin Fee]
FROM tbl_ARI_Insights_Rental_Downloads

Then using that
SELECT *,
, IIf([Month Booked]<[Last Pmt Date Long],
[Cap Cost]-[Term Payment]*[Mos Bill],0) AS [Curr Cap Cost]
FROM QueryOne

etc



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Vylent said:
I've been working on this query and report for several days now, to no avail.
Any and all suggestions would be extremely grateful for!

First, this query is pulling information from just one table. The main
thing I'm trying to eventually do is to get a sum for each of the years on
the report, but every time I try to do a sum in the Total field, I keep
getting this error. I've also tried using a crosstab query, and get the same
error. I've tried creating another query off of this query to see if I could
pinpoint what it is, but the best I can come up with is it's either not
liking the dates I have in there, or the combined information I have to get
the "Total Payment Forward" - Below is the whole SQL for the 1st and main
query (I wasn't sure if I should just cut and paste the one thing I'm most
concerned about or if the whole picture would be needed to understand the
whole problem) -

SELECT tbl_ARI_Insights_Rental_Downloads.[Month Booked], [In Service],
tbl_ARI_Insights_Rental_Downloads.Client,
tbl_ARI_Insights_Rental_Downloads.Vehicle,
tbl_ARI_Insights_Rental_Downloads.Div,
tbl_ARI_Insights_Rental_Downloads.Yard,
tbl_ARI_Insights_Rental_Downloads.[Bill Client Data],
tbl_ARI_Insights_Rental_Downloads.[Rental Amt],
tbl_ARI_Insights_Rental_Downloads.[Interest Amt],
tbl_ARI_Insights_Rental_Downloads.[Tax Amt],
tbl_ARI_Insights_Rental_Downloads.[Mgmt Fee],
tbl_ARI_Insights_Rental_Downloads.[Ins Fee],
tbl_ARI_Insights_Rental_Downloads.[Total Billed],
tbl_ARI_Insights_Rental_Downloads.[Invoice Number],
tbl_ARI_Insights_Rental_Downloads.[Invoice Date],
tbl_ARI_Insights_Rental_Downloads.[Cap Cost],
tbl_ARI_Insights_Rental_Downloads.[Book Value],
tbl_ARI_Insights_Rental_Downloads.[Mos Bill],
tbl_ARI_Insights_Rental_Downloads.[1st Bill],
tbl_ARI_Insights_Rental_Downloads.[Last Bill],
tbl_ARI_Insights_Rental_Downloads.[CLIENT DATA1],
tbl_ARI_Insights_Rental_Downloads.VIN,
tbl_ARI_Insights_Rental_Downloads.[Lease Term],
tbl_ARI_Insights_Rental_Downloads.[AFE Number], IIf([Lease
Term]=0,0,IIf([Lease Term]=36,[Cap Cost]*0.02778,IIf([Lease Term]=44,[Cap
Cost]*0.0225,IIf([Lease Term]=50,[Cap Cost]*0.0199987,IIf([Lease
Term]=55,[Cap Cost]*0.01819,IIf([Lease Term]=60,[Cap Cost]*0.01667)))))) AS
[Term Payment], [Cap Cost]/1000*0.4 AS [Admin Fee], IIf([Month Booked]<[Last
Pmt Date Long],[Cap Cost]-[Term Payment]*[Mos Bill],0) AS [Curr Cap Cost],
[Curr Cap Cost]*(0.053472737/12) AS [Curr Interest], IIf([Month Booked]<[Last
Pmt Date Long],[Term Payment]+[Admin Fee]+[Curr Interest],0) AS [Total
Payment Forward], DateAdd("m",+1,[In Service]) AS [1st Pmt Date Long],
DateAdd("m",[Lease Term],[1st Pmt Date Long]) AS [Last Pmt Date Long],
Format$([1st Pmt Date Long],"mmm-yyyy",0,0) AS [1st Pmt Date], Format$([Last
Pmt Date Long],"mmm-yyyy",0,0) AS [Last Pmt Date]
FROM tbl_ARI_Insights_Rental_Downloads
GROUP BY tbl_ARI_Insights_Rental_Downloads.[Month Booked],
tbl_ARI_Insights_Rental_Downloads.[In Service],
tbl_ARI_Insights_Rental_Downloads.Client,
tbl_ARI_Insights_Rental_Downloads.Vehicle,
tbl_ARI_Insights_Rental_Downloads.Div,
tbl_ARI_Insights_Rental_Downloads.Yard,
tbl_ARI_Insights_Rental_Downloads.[Bill Client Data],
tbl_ARI_Insights_Rental_Downloads.[Rental Amt],
tbl_ARI_Insights_Rental_Downloads.[Interest Amt],
tbl_ARI_Insights_Rental_Downloads.[Tax Amt],
tbl_ARI_Insights_Rental_Downloads.[Mgmt Fee],
tbl_ARI_Insights_Rental_Downloads.[Ins Fee],
tbl_ARI_Insights_Rental_Downloads.[Total Billed],
tbl_ARI_Insights_Rental_Downloads.[Invoice Number],
tbl_ARI_Insights_Rental_Downloads.[Invoice Date],
tbl_ARI_Insights_Rental_Downloads.[Cap Cost],
tbl_ARI_Insights_Rental_Downloads.[Book Value],
tbl_ARI_Insights_Rental_Downloads.[Mos Bill],
tbl_ARI_Insights_Rental_Downloads.[1st Bill],
tbl_ARI_Insights_Rental_Downloads.[Last Bill],
tbl_ARI_Insights_Rental_Downloads.[CLIENT DATA1],
tbl_ARI_Insights_Rental_Downloads.VIN,
tbl_ARI_Insights_Rental_Downloads.[Lease Term],
tbl_ARI_Insights_Rental_Downloads.[AFE Number];


What do I need to do to allow my query to use the sum function? Or even use
the crosstab function?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Type Mismatch in Criteria Expression 2
Date Expressions & Calculations 3
Filters within Queries 1
Iif, And Expressions in a Query (Working with Dates) 4
Error: Data type mismatch in criteria expression 0
Data Type Mismatch in Criteria Expression 17
data type mismatch in criteria expression 2
CDate results in Data Type Mismatch in Query & Excel Pivot 1

Top