I have a report that is based on totals query (which is based on another query). This ran just fine on Friday, but today I'm getting the "data type mismatch" error.
The base query, "qryProoflisting" runs just fine. This takes the date (which is a text field) computes the quarter, GP, etc. along with filtering out some lines. The second query, "qryProofCustSummary" which is just a totals query of the first one that runs just fine - is the one producing the error, and I can't figure out why.
qryProoflisting: (runs just fine)
---------------------------------------------------------
SELECT [YTD Proof Listing - 2003].[Sales Area], [YTD Proof Listing - 2003].[Business Partner], [YTD Proof Listing - 2003].[Business Partner Name], [YTD Proof Listing - 2003].[Employee Number], tblSalesReps.SalesRep, Format([Invoice Date],"00\/00\/0000") AS InvDate, Format([InvDate],"mmmm") AS Month, Format([InvDate],"yyyy") AS Year, DatePart("q",[invdate]) AS Qtr, [YTD Proof Listing - 2003].Item, IIf([Product Line]="","NONE",[Product Line]) AS ProdLine, [YTD Proof Listing - 2003].[PL Sales], [YTD Proof Listing - 2003].[PL Cost], [PL sales]-[PL cost] AS GP, IIf([item] Like "CW*" And [PL sales]<>0,"No") AS LineFlag
FROM [YTD Proof Listing - 2003] LEFT JOIN tblSalesReps ON [YTD Proof Listing - 2003].[Employee Number] = tblSalesReps.[Employee#]
WHERE (((IIf([item] Like "CW*" And [PL sales]<>0,"No")) Is Null))
ORDER BY [YTD Proof Listing - 2003].[Business Partner];
----------------------------------------------------------
qryProofCustSummary: (generates the error)
-----------------------------------------------------------------------
SELECT qryProofListing.[Sales Area], qryProofListing.[Business Partner], qryProofListing.[Business Partner Name], qryProofListing.SalesRep, qryProofListing.Qtr, qryProofListing.Year, Sum(qryProofListing.[PL Sales]) AS [SumOfPL Sales], Sum(qryProofListing.[PL Cost]) AS [SumOfPL Cost], Sum(qryProofListing.GP) AS SumOfGP
FROM qryProofListing
GROUP BY qryProofListing.[Sales Area], qryProofListing.[Business Partner], qryProofListing.[Business Partner Name], qryProofListing.SalesRep, qryProofListing.Qtr, qryProofListing.Year;
The base query, "qryProoflisting" runs just fine. This takes the date (which is a text field) computes the quarter, GP, etc. along with filtering out some lines. The second query, "qryProofCustSummary" which is just a totals query of the first one that runs just fine - is the one producing the error, and I can't figure out why.
qryProoflisting: (runs just fine)
---------------------------------------------------------
SELECT [YTD Proof Listing - 2003].[Sales Area], [YTD Proof Listing - 2003].[Business Partner], [YTD Proof Listing - 2003].[Business Partner Name], [YTD Proof Listing - 2003].[Employee Number], tblSalesReps.SalesRep, Format([Invoice Date],"00\/00\/0000") AS InvDate, Format([InvDate],"mmmm") AS Month, Format([InvDate],"yyyy") AS Year, DatePart("q",[invdate]) AS Qtr, [YTD Proof Listing - 2003].Item, IIf([Product Line]="","NONE",[Product Line]) AS ProdLine, [YTD Proof Listing - 2003].[PL Sales], [YTD Proof Listing - 2003].[PL Cost], [PL sales]-[PL cost] AS GP, IIf([item] Like "CW*" And [PL sales]<>0,"No") AS LineFlag
FROM [YTD Proof Listing - 2003] LEFT JOIN tblSalesReps ON [YTD Proof Listing - 2003].[Employee Number] = tblSalesReps.[Employee#]
WHERE (((IIf([item] Like "CW*" And [PL sales]<>0,"No")) Is Null))
ORDER BY [YTD Proof Listing - 2003].[Business Partner];
----------------------------------------------------------
qryProofCustSummary: (generates the error)
-----------------------------------------------------------------------
SELECT qryProofListing.[Sales Area], qryProofListing.[Business Partner], qryProofListing.[Business Partner Name], qryProofListing.SalesRep, qryProofListing.Qtr, qryProofListing.Year, Sum(qryProofListing.[PL Sales]) AS [SumOfPL Sales], Sum(qryProofListing.[PL Cost]) AS [SumOfPL Cost], Sum(qryProofListing.GP) AS SumOfGP
FROM qryProofListing
GROUP BY qryProofListing.[Sales Area], qryProofListing.[Business Partner], qryProofListing.[Business Partner Name], qryProofListing.SalesRep, qryProofListing.Qtr, qryProofListing.Year;
