The query could contain 0 or be null. I have shown the full code below
which works all but when I place CINT around the formula
[DEBTMAIN]![CURRBAL]/[SALEMAIN]![TURNOVER])*7 AS [Calc DOC].....
The error suggests using a variable but I don't know how to go about this.
Thanks for any pointers. Rob
SELECT DEBTMAIN.ACCNO AS [Acc No], ACCTMAIN.ACCNAME AS [Account Name],
HBM.HBMINITIAL AS [Acc Mgr], LOCATION.Loc_Name AS Location, ACCTMAIN.AREA AS
Territory, DEBTMAIN.WKEND AS Week, SALEMAIN.TURNOVER AS [Wk Turnover £],
([DEBTMAIN]![CURRBAL]/[SALEMAIN]![TURNOVER])*7 AS [Calc DOC], DEBTMAIN.DAYS
AS [JBA DOC], DEBTMAIN.CURRBAL AS [Current Bal], DEBTMAIN.CURRMNTH AS
[Current Mth], DEBTMAIN.CURRMNTH1 AS [Current -1], DEBTMAIN.CURRMNTH2 AS
[Current -2], DEBTMAIN.CURRMNTH3 AS [Current -3], DEBTMAIN.CURRMNTH4 AS
[Current -4], DEBTMAIN.CURRMNTH5 AS [Current -5]
FROM (((DEBTMAIN LEFT JOIN SALEMAIN ON (DEBTMAIN.WKEND = SALEMAIN.WKEND) AND
(DEBTMAIN.ACCNO = SALEMAIN.ACCNO)) INNER JOIN ACCTMAIN ON DEBTMAIN.ACCNO =
ACCTMAIN.ACCNO) INNER JOIN HBM ON ACCTMAIN.HBM = HBM.HBMID) LEFT JOIN
LOCATION ON ACCTMAIN.DEPOT = LOCATION.Loc_Code
WHERE (((DEBTMAIN.WKEND)=[Enter Week dd/mm/yyyy:]))
UNION SELECT SALEMAIN.ACCNO, ACCTMAIN.ACCNAME, HBM.HBMINITIAL,
LOCATION.Loc_Name, ACCTMAIN.AREA, SALEMAIN.WKEND, SALEMAIN.TURNOVER,
([DEBTMAIN]![CURRBAL]/[SALEMAIN]![TURNOVER])*7 AS [Calc DOC], DEBTMAIN.DAYS,
DEBTMAIN.CURRBAL, DEBTMAIN.CURRMNTH, DEBTMAIN.CURRMNTH1, DEBTMAIN.CURRMNTH2,
DEBTMAIN.CURRMNTH3, DEBTMAIN.CURRMNTH4, DEBTMAIN.CURRMNTH5
FROM ((DEBTMAIN RIGHT JOIN SALEMAIN ON (DEBTMAIN.ACCNO = SALEMAIN.ACCNO) AND
(DEBTMAIN.WKEND = SALEMAIN.WKEND)) INNER JOIN (ACCTMAIN INNER JOIN HBM ON
ACCTMAIN.HBM = HBM.HBMID) ON SALEMAIN.ACCNO = ACCTMAIN.ACCNO) LEFT JOIN
LOCATION ON ACCTMAIN.DEPOT = LOCATION.Loc_Code
WHERE (((SALEMAIN.WKEND)=[Enter Week dd/mm/yyyy:]) AND ((DEBTMAIN.WKEND) Is
Null))
ORDER BY [Acc Mgr], [Calc DOC] DESC;
Duane Hookom said:
Is the SALE!TURNOVER field ever Null or 0?
--
Duane Hookom
Microsoft Access MVP
Rob said:
Hi, Using Access 2000. I have a statement in a Union Query that is
([DEBT]![BAL]/[SALE]![TURNOVER])*7 AS [Calc DOC] which works albeit I
want
to round the result, I have added CINT but running the query now fails as
says the query maybe to complex, try setting variables
CANT(([DEBTMAIN]![CURRBAL]/[SALEMAIN]![TURNOVER])*7) AS [Calc DOC].
The CINT formula is shown 3 times in the SQL so a variable may work,
albeit
I can't find how to set this up.
Any pointers would be helpful. Thanks Rob.
)