How to auto-fill based on complex conditional (1 Viewer)

Scatter

Registered User.
Local time
Today, 06:23
Joined
Dec 19, 2012
Messages
11
In A2010, I'm trying to auto fill a table field, Status, based on a somewhat complex conditional (think when library books are due, though that's not my situation):


IF DateRcvd is null AND DueDate < today THEN [book checked out and due but was never returned]
Status = “overdue”
ELSEIF DateRcvd is null AND DueDate > today THEN [book is checked out but not due yet]
Status = “not due”
ELSEIF DateRcvd > DueDate THEN [book returned late]
Status = “overdue”
ELSEIF DateRcvd < DueDate then [book returned on time]
Status = “on time”
ELSE
Status = “ “
END IF


This last ELSE Status="" may not be needed, but I'm explicitly trying to cover all situations.


DateRcvd is a Date field; DueDate is itself calculated based on the text field, PermitYear ("12/31/" & [PermitYear]), all in the table Permits.


Optimally, I would like the Status field to auto update in a DS form when the DateRcvd is updated. My brute force solutions have only been able to implement a portion of this, so I would truly appreciate help!
 

plog

Banishment Pending
Local time
Today, 08:23
Joined
May 11, 2011
Messages
11,613
First, you shouldn't store calculated values, so you don't store the status value in a field, nor should you store the DueDate field. Instead you should calculate them when you need them.

Second, why is PermitYear text? You forsee everyone converting to non-numerical way of naming years? Make it a number.

For your situation, my suggestion would be to create 2 functions in a module (1 to calculate Due Date and the other Status), then wherever you need that data (query, form, report, or even another function) you pass it the data it needs to do the calculations and the functions would return the correct value.

First, build a function to determine due date. Then build the function to calculate status. Using a function makes it much easier to implement your logic, understand and debug it. Give it a shot and if you have any trouble post back here what you have.
 

pr2-eugin

Super Moderator
Local time
Today, 13:23
Joined
Nov 30, 2011
Messages
8,494
Do not fill the field with this information.. In fact do not even store Due date information.. Calculated fields belong to Queries, not tables.. Allen Browne talks more about this.. http://allenbrowne.com/casu-14.html

Do this calculation when you need it in Queries and Unbound form controls.. Do not store it..
 

Users who are viewing this thread

Top Bottom