SELECT IIf(ODBC_CUSTOMER_VIEW!Character01='1','DEFENSE','COMMERCIAL') AS
CompanyClass, PUB_SalesRep.Name,
IIf(IsNull(ODBC_CUSTOMER_VIEW!Character02),'OTHER',ODBC_CUSTOMER_VIEW!Character02)
AS CompanyGroup, IIf(IsNull(PUB_Part!UserChar3),'OTHER',PUB_Part!UserChar3)
AS Platform, ODBC_CUSTOMER_VIEW.CustID, ODBC_CUSTOMER_VIEW.Name,
Year(PUB_OrderRel!ReqDate) AS DueYear, Month(PUB_OrderRel!ReqDate) AS
DueMonth,
PUB_OrderRel!OurReqQty-PUB_OrderRel!OurJobShippedQty-PUB_OrderRel!OurStockShippedQty
AS Balance, PUB_OrderDtl!UnitPrice*[Balance] AS Sales,
IIf([DueYear]>[CurrentYear],[DueMonth],IIf([DueMonth]<[CurrentMonth],[CurrentMonth],[DueMonth]))
AS SalesMonth, IIf([SalesMonth]=1,[Sales],0) AS JanSales,
IIf([SalesMonth]=2,[Sales],0) AS FebSales, IIf([SalesMonth]=3,[Sales],0) AS
MarSales, IIf([SalesMonth]=4,[Sales],0) AS AprSales,
IIf([SalesMonth]=5,[Sales],0) AS MaySales, IIf([SalesMonth]=6,[Sales],0) AS
JunSales, IIf([SalesMonth]=7,[Sales],0) AS JulSales,
IIf([SalesMonth]=8,[Sales],0) AS AugSales, IIf([SalesMonth]=9,[Sales],0) AS
SepSales, IIf([SalesMonth]=10,[Sales],0) AS OctSales,
IIf([SalesMonth]=11,[Sales],0) AS NovSales, IIf([SalesMonth]=12,[Sales],0)
AS DecSales, Month(Date()) AS CurrentMonth,
IIf([DueMonth]<[CurrentMonth],[CurrentMonth],[DueMonth]) AS SalesMonthBkup,
PUB_OrderDtl.PartNum, ODBC_CUSTOMER_VIEW.SalesRepCode,
[JanSales]+[FebSales]+[MarSales]+[AprSales]+[MaySales]+[JunSales]+[JulSales]+[AugSales]+[SepSales]+[OctSales]+[NovSales]+[DecSales]
AS YearlySales, Year(Date()) AS CurrentYear, PUB_OrderRel.ReqDate
FROM ((((ODBC_CUSTOMER_VIEW INNER JOIN PUB_OrderHed ON
(ODBC_CUSTOMER_VIEW.CustNum = PUB_OrderHed.CustNum) AND
(ODBC_CUSTOMER_VIEW.Company = PUB_OrderHed.Company)) INNER JOIN PUB_OrderDtl
ON (PUB_OrderHed.Company = PUB_OrderDtl.Company) AND (PUB_OrderHed.OrderNum
= PUB_OrderDtl.OrderNum)) INNER JOIN PUB_OrderRel ON (PUB_OrderDtl.OrderNum
= PUB_OrderRel.OrderNum) AND (PUB_OrderDtl.Company = PUB_OrderRel.Company)
AND (PUB_OrderDtl.OrderLine = PUB_OrderRel.OrderLine)) LEFT JOIN
PUB_SalesRep ON (ODBC_CUSTOMER_VIEW.SalesRepCode =
PUB_SalesRep.SalesRepCode) AND (ODBC_CUSTOMER_VIEW.Company =
PUB_SalesRep.Company)) LEFT JOIN PUB_Part ON (PUB_OrderDtl.Company =
PUB_Part.Company) AND (PUB_OrderDtl.PartNum = PUB_Part.PartNum)
This is where the issue is:
WHERE
(((Year([PUB_OrderRel]![ReqDate]))=[Forms]![frmSalesAnalysis]![StartDate])
AND ((ODBC_CUSTOMER_VIEW.Company)="Loc") AND ((PUB_OrderHed.OpenOrder)=Yes)
AND ((PUB_OrderDtl.OpenLine)=Yes) AND ((PUB_OrderRel.OpenRelease)=Yes))
ORDER BY IIf(ODBC_CUSTOMER_VIEW!Character01='1','DEFENSE','COMMERCIAL') DESC
, PUB_SalesRep.Name,
IIf(IsNull(ODBC_CUSTOMER_VIEW!Character02),'OTHER',ODBC_CUSTOMER_VIEW!Character02),
IIf(IsNull(PUB_Part!UserChar3),'OTHER',PUB_Part!UserChar3),
ODBC_CUSTOMER_VIEW.CustID, Year(PUB_OrderRel!ReqDate),
Month(PUB_OrderRel!ReqDate), PUB_OrderRel.ReqDate;
Thanks,
Jasper
KARL DEWEY said:
Post SQL of query by opening in design view, click on VIEW - SQL View,
hightlight all, copy, and paste in a post.
--
Build a little, test a little.
Jasper Recto said:
I have a query that is filtered out by a year.
I have a form that the user enters in the year.
In the query, if I just put in a year of 2009. It works fine. If I
replace
the filter criteria to this:
[Forms]![frmSalesAnalysis]![Year]
I get a ODBC-Failed error.
Any ideas.
Thanks,
Jasper
.
.
