Results 1 to 6 of 6
  1. #1
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93

    Iif Statement with Two Criteria in calculated Field of a Query?

    Hi Accessors!



    I am attempting to formulate a calculated field with an Iif statement with two criteria and it will not calculate and there are no errors flagged after running the query. Please guide me in what needs to be fixed.

    Code:
    Expr1: IIf(Date()>[Query_RPT_Annual_Report_Tracking]![Anniversary Date] & [Query_RPT_Annual_Report_Tracking]![Date Report Received]=Null,"Overdue","")
    Thanks!!

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    The "&" is concatenate two strings.

    The "AND" is what you need I think.

    The "=Null" should be ISNULL([Query_RPT_Annual_Report_Tracking]![Date Report Received]).

  3. #3
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    ifpm,

    Thank you- it got the query to work properly.

    However, when I create a report on the query, the report will load but will not present the data- the report opens and it stays white blank and the mouse remains in hourglass mode forever. I had to manually force shut the database and re-open. It will not work.

    Am I doing something wrong? I used Report Wizard when I created a report on the query.

  4. #4
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    Is it because I do not have a SQL behind the report? If so, how could I write such a line?!

  5. #5
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    If you use the report wizard and select the query to build the report, there is no reason the report should not work.

    Couple things to check:
    1) Does the query runs and produce the result as expected?
    2) Try to compact and repair and re-create the report using the query and see if it works.

    If that still does not work, can you post the database (remove all the secure data of course).

  6. #6
    excellenthelp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    93
    I have decided to make a new query since it was pulling multiple records all over the place when it was not supposed to do. However, thanks for the help, lfpm! Keep it up.

    You've earned a rep point!

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

Similar Threads

  1. Replies: 8
    Last Post: 06-09-2014, 01:14 PM
  2. If then statement on calculated field
    By blakej in forum Access
    Replies: 9
    Last Post: 03-17-2014, 01:12 PM
  3. IIf Statement in Query Criteria
    By kyle87 in forum Queries
    Replies: 8
    Last Post: 07-29-2013, 12:31 PM
  4. Replies: 1
    Last Post: 05-05-2010, 01:54 AM
  5. Setting criteria in a calculated date field
    By SteveReberger in forum Access
    Replies: 0
    Last Post: 10-29-2008, 06:58 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