Solved Due in date (1 Viewer)

Teri Bridges

Member
Local time
Today, 06:40
Joined
Feb 21, 2022
Messages
186
I am working to create a due in date. I have a field that is the target date and I have a status ID field.
I want the due in date to tell me how many days until the task is due or subsequently how may days overdue. So I am building off the idea that if the status is complete return 0 if the status is not complete return the the difference between the target date and today's date. I have written the following and I get the days due counted correctly, but it is not returning the 0 if the status is set to complete.

=IIf([statusID]&""="complete",0,DateDiff("d",Date(),[TargetDate]))
 

Attachments

  • due In Date.png
    due In Date.png
    105.3 KB · Views: 188
  • due In Date2.png
    due In Date2.png
    26.3 KB · Views: 192

theDBguy

I’m here to help
Staff member
Local time
Today, 04:40
Joined
Oct 29, 2018
Messages
21,358
Is the status "ID" value a 0 or "complete?"
 

Teri Bridges

Member
Local time
Today, 06:40
Joined
Feb 21, 2022
Messages
186
I am working to create a due in date. I have a field that is the target date and I have a status ID field.
I want the due in date to tell me how many days until the task is due or subsequently how may days overdue. So I am building off the idea that if the status is complete return 0 if the status is not complete return the the difference between the target date and today's date. I have written the following and I get the days due counted correctly, but it is not returning the 0 if the status is set to complete.

=IIf([statusID]&""="complete",0,DateDiff("d",Date(),[TargetDate]))
I made that a combo box store the ID but select from the value list.
So the user sees the value not the ID.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:40
Joined
Oct 29, 2018
Messages
21,358
I made that a combo box store the ID but select from the value list.
So the user sees the value not the ID.
You did that for the benefit of the users. However, your code should use what the database sees (i.e. the number). Have you tried that?
 

Teri Bridges

Member
Local time
Today, 06:40
Joined
Feb 21, 2022
Messages
186
You did that for the benefit of the users. However, your code should use what the database sees (i.e. the number). Have you tried that?
Okay, that was it. Boy what an idiot I am. Sorry for the trouble.
=IIf([statusID]&""="10",0,DateDiff("d",Date(),[TargetDate]))
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:40
Joined
Oct 29, 2018
Messages
21,358
Okay, that was it. Boy what an idiot I am. Sorry for the trouble.
=IIf([statusID]&""="10",0,DateDiff("d",Date(),[TargetDate]))
Hi. No worries. We all make mistakes sometimes. Good luck with your project.
 

Users who are viewing this thread

Top Bottom