Results 1 to 10 of 10
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Syntax on Iif Statement?


    I am trying to tally Last year and This year's totals in one query

    Last Year should be the total on the PaidYear=Year(Date())-1 WHERE the EndYear=Year(Date())
    This Year should be the total on the PaidYear=Year(Date()) WHERE the EndYear>=Year(Date())+1

    I currently have this for the above but it doesn't seem to be producing the correct totals.
    I exported out the details and summed it up in Excel.
    This Year in the criteria below is showing less than the details sum show.

    What is the correct syntax?
    Code:
    LastYear: Sum(IIf([PaidYear]=Year(Date())-1,[EndYear]=Year(Date()),[ExtPrice])), ThisYear: Sum((IIf([PaidYear]=[Forms]![Main]![PaymentYear],[EndYear]>=Year(Date())+1,[ExtPrice])))

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    IIF statement cannot assign data to fields:
    [endyear]=year(date)

    It only displays data.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok, what's the correct syntax?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The IIF() function is used for decision making. The syntax is
    IIf(expr, truepart, falsepart)

    The "expr" must evaluate to a true or false value. So, for last year, you have "[PaidYear]=Year(Date())-1"
    Which means if the field "PaidYear" equals this year -1, the result is TRUE, so return the truepart, otherwise return the falsepart.

    You want last years "ExtPrice", so the expression would be:
    Code:
    LastYear: IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)
    This means if the "PaidYear" equals last year, return the "ExtPrice", else return 0 (zero).


    For this year, the expression would be:
    Code:
    ThisYear: IIf([PaidYear]=Year(Date()),[ExtPrice],0)

    Lets say there is a table, "tblPayments", with 2 fields: "Paidyear" and "ExtPrice".
    There is a query "qryPayments", with the SQL of
    Code:
    SELECT tblPayments.PaidYear, tblPayments.ExtPrice
    FROM tblPayments
    ORDER BY tblPayments.PaidYear;
    When you execute the query you see the year paid and the amount.
    Click image for larger version. 

Name:	Ex1.jpg 
Views:	14 
Size:	17.4 KB 
ID:	29376


    The query is modified to show last year and this year amounts using the expressions above.
    It now looks like
    Code:
    SELECT tblPayments.PaidYear, tblPayments.ExtPrice, IIf([PaidYear]=Year(Date())-1,[ExtPrice],0) AS LastYear, IIf([PaidYear]=Year(Date()),[ExtPrice],0) AS ThisYear
    FROM tblPayments
    ORDER BY tblPayments.PaidYear;
    Click image for larger version. 

Name:	Ex2.jpg 
Views:	15 
Size:	26.1 KB 
ID:	29377

    To get just the year total, the query must be changed to a totals query.
    The SQL would be
    Code:
    SELECT tblPayments.PaidYear, Sum(IIf([PaidYear]=Year(Date()),[ExtPrice],0)) AS ThisYear, Sum(IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)) AS LastYear
    FROM tblPayments
    GROUP BY tblPayments.PaidYear;
    Click image for larger version. 

Name:	Totals.png 
Views:	15 
Size:	27.6 KB 
ID:	29378


    To have only one record, the "PaidYear" column would have to be removed:
    Code:
    SELECT Sum(IIf([PaidYear]=Year(Date()),[ExtPrice],0)) AS ThisYear, Sum(IIf([PaidYear]=Year(Date())-1,[ExtPrice],0)) AS LastYear
    FROM tblPayments;
    Click image for larger version. 

Name:	TotalOnly.png 
Views:	15 
Size:	18.7 KB 
ID:	29379



    Hope this helps.......

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I have that when the [EndYear] portion is removed.
    I added this to try and get the results based on the WHERE

    Code:
    LastYear: Sum(IIf([PaidYear]=Year(Date())-1,[EndYear]=Year(Date()),[ExtPrice])), ThisYear: Sum((IIf([PaidYear]=[Forms]![Main]![PaymentYear],[EndYear]>=Year(Date())+1,[ExtPrice])))
    What i need is how to incorporate the EndYear to the existing code

    I am unsure how to add this
    WHERE the EndYear>=Year(Date())+1 on the This Year count and sum
    WHERE the EndYear>=Year(Date()) on the Last Year count and sum

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    What about the
    WHERE the EndYear>=Year(Date())+1 on the This Year
    WHERE the EndYear>=Year(Date()) on the Last Year

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, lets try this:

    In a query, you add one or more tables, then add fields to the grid. If you want to change the name of an existing field you prefix it with something like
    Code:
    SurName:  LastName
    Note the colon!

    You can "create" a new column (a calculated column) using:
    Code:
    FullName: LastName & ", " & FirstName
    where LastName and FirstName are existing fields in a table.

    You can do
    Code:
    ExtendedPrice: Quantity * Price

    YOU CANNOT HAVE
    Code:
    LastYear: Sum(IIf([PaidYear]=Year(Date())-1,[EndYear]=Year(Date()),[ExtPrice])), ThisYear: Sum((IIf([PaidYear]=[Forms]![Main]![PaymentYear],[EndYear]>=Year(Date())+1,[ExtPrice])))
    in one column in a query!
    You can take out ThisYear: and calculate this year's total..


    I am failing to understand why you are worried about EndYear? (You haven't provided the SQL of the query or an example data set with expected results.
    "PaidDate" should be 1 year. Right?


    Maybe you could step back and explain what you are trying to do like you would to a 10 year old. (Not HOW you are wanting to do it)

  8. #8
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    The LastYear and ThisYear are labels only.
    They are NOT part of the syntax.

    The EndYear is to identify which payments were made for which membership EndYear (AKA: membership end date, EndYear is derived from Year([EndDate])

    So you could have paid last year, in 2016 for membership ending in 2016 and I could have paid last year, in 2016 for membership ending this year, in 2017, etc ...

    Why I need to have counts and total paid for year paid to the year memberships are ending

    Why I was trying to do the following
    Code:
    WHERE the EndYear>=Year(Date())+1 on the ThisYear
    WHERE the EndYear>=Year(Date()) on the LastYear
    LastYear needs to have the total count and total paid in 2016 where the EndYear is the current year, 2017
    ThisYear needs to have the total count and total paid in 2017 where the EndYear is the for future years from this year

    hope this explains it better.

    I appreciate the thoroughness of your explanations and examples! It'll be helpful to others as well

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Does anyone know how to do that?

    LastYear needs to have the total count and total paid in 2016 where the EndYear is the current year, 2017
    ThisYear needs to have the total count and total paid in 2017 where the EndYear is the for future years from this year

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Very sorry I am not understanding correctly or I am not explaining myself clearly enough.

    I'll step back and hopefully someone else will be able to help you with the problem....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. syntax for if then statement
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 09-05-2013, 03:33 PM
  2. Syntax of rs.Open Statement
    By Philosophaie in forum Access
    Replies: 5
    Last Post: 07-05-2013, 06:24 PM
  3. If statement syntax help!
    By Richie27 in forum Programming
    Replies: 15
    Last Post: 06-15-2012, 12:58 AM
  4. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  5. IIf Statement Syntax Error
    By shexe in forum Queries
    Replies: 2
    Last Post: 10-06-2010, 08:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums