The main query looks like:
Code:
SELECT QuoteID, Sum(QuoteTotal) AS SumOfQuoteTotal
FROM (QueryFromYourUnionQuery)
GROUP BY QuoteID;
Advice number 1 - create a saved query from your Union query. Like:
Code:
YourUnionQuery =
SELECT tblQuoteLineItems.QuoteID, Sum(tblQuoteLineItems.[LineTotal]) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=No))
UNION ALL
SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[defaultproducttaxrate])) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=Yes)))
GROUP BY QuoteID
UNION ALL
SELECT QuoteID, Sum(MUTotal) AS QuoteTotal
FROM (SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=No))
UNION ALL
SELECT tblQuoteLineItems.QuoteID, Sum(([LineTotal]*(1+[defaultproducttaxrate]))*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=Yes)))
Now your main query (make it saved one too - but probably you have done this anyway) will look like:
Code:
qryQuoteTotal =
SELECT QuoteID, Sum(QuoteTotal) AS SumOfQuoteTotal
FROM YourUnionQuery
GROUP BY QuoteID;
The formula in text box is returning a string, because one possible return value of IIF() is empty string. And poor Access is desperately looking for number ""
Advice number 2: replace "" for return value with 0
Code:
=IIf([lstQuoteName]="",0,DLookUp("[SumofQuoteTotal]","qryQuoteTotal","[QuoteID]=" & [lstQuoteName]))
When the formula in text box is working slowly anyway, and the data returned by query must not be "at-the-current-time", then you can have a table for it's data. E.g. at nighttime a scheduled task opens the database, and runs a script which deletes all data from table, and inserts new data from query. Your tex-box Dlookup() searches table instead of query.