Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Query: IF date = current month, sum sales, 0) How do I make this work?

    I have a query that I need three columns One for this month, last month and 2 months ago. Instead of using a crosstab query I want to put this into a regular query.

    I figure the formula would work something like this

    IIF(Date = current month, sum(sales),0) Im not sure how to make the date=current month part work.

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Here is the code for placement into a query:
    Code:
    SumofSalesCurMonth:Sum(IIF(Month([yourDateFld]) = Month(Date()) And Year([yourDateFld]) = Year(Date()),[Sales],0))
    SumofSalesPrevMonth:Sum(IIF(Month([yourDateFld]) = Month(DateAdd("m",-1,Date())) And Year([yourDateFld]) = Year(DateAdd("m",-1,Date())),[Sales],0))
    SumofSales2MonthsAgo:Sum(IIF(Month([yourDateFld]) = Month(DateAdd("m",-2,Date())) And Year([yourDateFld]) = Year(DateAdd("m",-2,Date())),[Sales],0))
    Last edited by billmeye; 08-21-13 at 12:42. Reason: Add year

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Thank you so much for you help! Worked like a charm.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    That's great to hear.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •