Solved Due in date

[Teri Bridges]

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]))

 

[theDBguy]

Is the status "ID" value a 0 or "complete?"

 

[Teri Bridges]

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.

 

[Teri Bridges]

Is the status "ID" value a 0 or "complete?"

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 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]

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]

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.