I need to write a query to calculate four numbers:
LifeTime Sales (in dollars)
YTD Sales (in dollars)
I already have code that calculates the first day of the current year.
Here's what I have so far. The code below calculates three of the four values I need (LifeTime and YTD Orders, as well as Lifetime Sales in dollars), but I can't figure out how to calculate YTD sales in dollars with the same query.
'New code to calculate Orders (YTD and Lifetime) and Lifetime Dollars
strSQLtext = "SELECT Count(invoicenumber) AS LifeTimeOrders,"
strSQLtext = strSQLtext & "SUM(ABS(shippeddate > #" & dteYTDstartDate & "# )) AS YTDOrders, "
strSQLtext = strSQLtext & "SUM(price * Quantity) AS LifetimeDollars "
strSQLtext = strSQLtext & "FROM invoicehistory "
strSQLtext = strSQLtext & "where AccountNumber = " & Forms!ProspectInfo!AccountNumber & " And [quantity] <> 0;"