-
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:
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!
-
you need a nested if and an isblank
if(E2<if(isblank(d2),c2,d2),"yes","no")
-
Ah thank you so much for your help!
-
I've updated my query with the expression that you suggested. See below with actual field names from the table:
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:
Any ideas? Sorry to be such a novice! I'm sure I'm just being stupid!
Thanks again!
-
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.
-
Ah Ajax you're a star! Thanks again for your help and advice!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules