Results 1 to 6 of 6
  1. #1
    Jaq is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2016
    Posts
    7

    Help with IF query

    Hi all,



    I'm trying to build an expression within a query that shows if a decision has been made within a target deadline or not.

    I'm fine with adding in the IF formula if it's just a simple 'if the decision date is less than the target date show yes, if not show no' but the problem I'm having is that some of the decisions also have extensions to the target date.

    I've included a mock up of an example below:

    Click image for larger version. 

Name:	Screenshot 2016-09-25 at 17.41.30.png 
Views:	20 
Size:	10.5 KB 
ID:	25913

    So what I would need a formula to do is first check if there was an extension date and if this is the case use this to work out if the target was met or not based on the decision date. Then if there is no extension date, I'd need it to base the 'Target Met' column on the target date column.

    Sorry if that doesn't make sense - I think what I need is a nested IF but I have no idea how to write it!

    Thanks for your help in advance!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need a nested if and an isblank

    if(E2<if(isblank(d2),c2,d2),"yes","no")

  3. #3
    Jaq is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2016
    Posts
    7
    Ah thank you so much for your help!

  4. #4
    Jaq is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2016
    Posts
    7
    I've updated my query with the expression that you suggested. See below with actual field names from the table:

    Click image for larger version. 

Name:	Access Expression.PNG 
Views:	15 
Size:	20.7 KB 
ID:	25920

    As far as I can see I've built it correctly but I'm getting an error message when I try to run the query:

    Click image for larger version. 

Name:	Error Message.PNG 
Views:	15 
Size:	3.5 KB 
ID:	25921

    Any ideas? Sorry to be such a novice! I'm sure I'm just being stupid!

    Thanks again!

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my mistake, saw the excel and made a wrong assumption. Access uses iif, not if and does not have isblank, instead use the nz function

    TargetMet:iif(DecisionDate<nz(ExtensionDate,Target Date),"yes","no")

    note having spaces and non alphanumeric characters in field names is a bad idea in databases. Use the caption property in table or query fields to populate form/report labels with something with spaces.

  6. #6
    Jaq is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2016
    Posts
    7
    Ah Ajax you're a star! Thanks again for your help and advice!

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

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