Home

Calculated Fields with iif statements

%3CLINGO-SUB%20id%3D%22lingo-sub-822916%22%20slang%3D%22en-US%22%3ECalculated%20Fields%20with%20iif%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822916%22%20slang%3D%22en-US%22%3EHi%20all%2C%20I%20have%20a%20split%20DB%20with%204%20calculated%20fields%20that%20show%20the%20number%20of%20days%20between%20the%20dates%20entered%20in%20other%20fields.%20Sometimes%20the%20results%20are%20negative%20as%20one%20would%20expect.%20What%20I'd%20like%20to%20do%20is%20have%20the%20field%20set%20to%200%20if%20the%20calculation%20is%20less%20than%200%20to%20prevent%20any%20skewed%20reporting.%20I%20suspect%20I%20would%20use%20the%20IIf%20function%20to%20do%20this%2C%20but%20I'm%20not%20sure%20how%20to%20go%20about%20it.%20My%20attempts%20so%20far%20have%20returned%20improper%20syntax%20errors.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20expression%20for%20one%20of%20the%20fields%20is%3A%3CBR%20%2F%3EDay(%5BClose%20Date%5D)-Day(%5BCreated%20Date%5D)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20it%20as%3A%3CBR%20%2F%3EIIF(Day(%5BClose%20Date%5D)-Day(%5BCreated%20Date%5D)%26lt%3B%220%22%2C%5BDays%20Active%5D%3D%220%22)%3CBR%20%2F%3E%3CBR%20%2F%3EAm%20I%20able%20to%20add%20it%20on%20the%20expression%20or%20would%20it%20require%20a%20query%3F%20How%2C%20exactly%2C%20should%20I%20apply%20it%20to%20the%20fields%20so%20that%20it%20changes%20the%20result%20to%200%20in%20the%20table%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%2C%3CBR%20%2F%3EHeather%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-822916%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831417%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20Fields%20with%20iif%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831417%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20hrandall%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20need%20to%20use%20DateDiff()%20for%20this.%26nbsp%3B%20This%20is%20the%20formula%20I%20used%20to%20calculate%20the%20difference%20between%202%20dates%2C%20returning%20a%20%220%22%20if%20one%20date%20was%20less%20than%20another%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIIf(DateDiff(%22d%22%2C%5BStartDate%5D%2C%5BEndDate%5D)%26lt%3B%3D0%2C%220%22%2CDateDiff(%22d%22%2C%5BStartDate%5D%2C%5BEndDate%5D))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20this%20formula%20in%20the%20Control%20Source%20of%20your%20Form%20or%20Report%20to%20calculate%20the%20difference%20between%20the%20dates%2C%20returning%20a%20%220%22%20if%20the%20End%20Date%20is%20less%20than%20or%20equal%20to%20the%20Start%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351419%22%20target%3D%22_blank%22%3E%40hrandall%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
hrandall
Visitor
Hi all, I have a split DB with 4 calculated fields that show the number of days between the dates entered in other fields. Sometimes the results are negative as one would expect. What I'd like to do is have the field set to 0 if the calculation is less than 0 to prevent any skewed reporting. I suspect I would use the IIf function to do this, but I'm not sure how to go about it. My attempts so far have returned improper syntax errors.

My expression for one of the fields is:
Day([Close Date])-Day([Created Date])

I tried it as:
IIF(Day([Close Date])-Day([Created Date])<"0",[Days Active]="0")

Am I able to add it on the expression or would it require a query? How, exactly, should I apply it to the fields so that it changes the result to 0 in the table?

Thank you,
Heather
1 Reply

Hello hrandall,

 

I think you need to use DateDiff() for this.  This is the formula I used to calculate the difference between 2 dates, returning a "0" if one date was less than another:

 

=IIf(DateDiff("d",[StartDate],[EndDate])<=0,"0",DateDiff("d",[StartDate],[EndDate]))

 

Use this formula in the Control Source of your Form or Report to calculate the difference between the dates, returning a "0" if the End Date is less than or equal to the Start Date.

 

Hope this helps.

 

@hrandall 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies