UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Expression Builder Problems, Access 2010    
 
   
ChrisBalsom
post Aug 3 2017, 11:15 PM
Post#1



Posts: 27
Joined: 9-April 15



I am currently trying to get a form to show a specific status of an item based on an expression.
If item is due with in 45 days of next due date I want it to show as due.
If the item has been checked as completed then it will show as completed.
If the item is past next due date it will show as overdue.
If the item is not completed but not within 45 days of due date then it should show current.

Currently this is not working.

=IIf([Completed]=-1,"Completed",IIf([NextDueDate]<45,"DUE",IIf([NextDueDate]>Date(),"Overdue","Current")))

see picture
Attached File  snippet.JPG ( 29.62K )Number of downloads: 0


Top one should show due and next 2 should show current.

Any help would be appreciated
This post has been edited by ChrisBalsom: Aug 3 2017, 11:16 PM
Go to the top of the page
 
Auntie Jack
post Aug 4 2017, 01:18 AM
Post#2



Posts: 125
Joined: 10-March 10



Hi,

Try something like this (in a rush so haven't tested): =IIf([Completed]=-1,"Completed",IIf(DateDiff("d",Date(),[NextDueDate])<45,"DUE",IIf(DateDiff("d",Date(),[NextDueDate])>0,"Overdue","Current")))

Jack
Go to the top of the page
 
ChrisBalsom
post Aug 7 2017, 12:40 AM
Post#3



Posts: 27
Joined: 9-April 15



So, close. I've tried altering this slightly but it makes it all overdue

Attached File  Capture.JPG ( 28.61K )Number of downloads: 0
Go to the top of the page
 
ridders
post Aug 7 2017, 02:34 AM
Post#4



Posts: 114
Joined: 15-January 17
From: Somerset UK


Typing this on my phone so can't write the whole formula

After the IIF, try [NextDueDate]-Date<45, "Due" .....

--------------------
Colin
Go to the top of the page
 
ChrisBalsom
post Aug 7 2017, 02:39 AM
Post#5



Posts: 27
Joined: 9-April 15



Thanks ridders, but this just gives me #Name? for all entries.
This post has been edited by ChrisBalsom: Aug 7 2017, 02:48 AM
Go to the top of the page
 
ridders
post Aug 7 2017, 04:45 AM
Post#6



Posts: 114
Joined: 15-January 17
From: Somerset UK


The previous solution of yours didn't work as dates in the past automatically satisfy the first IIf condition so are displayed as 'DUE'
There may have been missing brackets in my first attempt done on my phone ...

Try this. It works for me

CODE
IIf([Completed]=-1,"Completed",IIf(([NextDueDate]-Date()) Between 0 And 45,"DUE",IIf([NextDueDate]<Date(),"Overdue","Current")))


Attached File  Capture.PNG ( 12.78K )Number of downloads: 2


NOTE:
expr1 has the formula above
expr2 just gets the value of [NextDueDate]-Date() so I could check it. Not needed in your solution
This post has been edited by ridders: Aug 7 2017, 05:13 AM

--------------------
Colin
Go to the top of the page
 
ChrisBalsom
post Aug 7 2017, 05:20 PM
Post#7



Posts: 27
Joined: 9-April 15



Ridders, YOU ARE AN ABSOLUTE LEGEND.

This works perfectly. Thanks so much.
Go to the top of the page
 
ridders
post Aug 7 2017, 05:33 PM
Post#8



Posts: 114
Joined: 15-January 17
From: Somerset UK


You're welcome

A leg-end in my own lifetime! Something's afoot!

--------------------
Colin
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th August 2017 - 10:41 AM