Query or Function to Update another field value

[steve21nj]

You store the DueDate. You CALCULATE the Status.

As I mentioned in the beginning, the Status can be manually updated. So I am working on calculated fields with manual entries. That's why I'm having trouble understanding the passing of values from unbound to the table when the user selects completed or review as a status.

 

[David R]

Okay, I see what you're saying now.

I will humbly suggest that you're trying to overuse one field for multiple purposes (deadline warnings are not the same as status, etc). You can store Completed/Review/In Process as your [Status] field and use a calculated control to warn about deadlines, or VBA to turn the form red, or whatever... the problem right now is it's going to say "Due in 48" UNTIL you run get_status(), which might not happen today, tomorrow, or for a week. A calculated control will update EVERY time it is referenced.

 

[steve21nj]

Okay, I see what you're saying now.

I will humbly suggest that you're trying to overuse one field for multiple purposes (deadline warnings are not the same as status, etc). You can store Completed/Review/In Process as your [Status] field and use a calculated control to warn about deadlines, or VBA to turn the form red, or whatever... the problem right now is it's going to say "Due in 48" UNTIL you run get_status(), which might not happen today, tomorrow, or for a week. A calculated control will update EVERY time it is referenced.

That make sense. I will have a text box set to unbound to display the calculated status, for the sake of it I will call the label [CalculatedStatus] and the text box field [Text45]. I will just disable the unbound control so the user cannot interact with it.

This will also allow the user to select the appropriate [status] to complete or review.

Which brings me back to my 3rd question from before. How do I loop the function for all the records on a continuous form. Right now it updates every record based off the selected record.

I tried creating the loop statement but it doesn't work, still updates every record based on the selected record. (Example, If the first calculated record should say 24 hours, every record will display 24 hours instead of the individual calculation.

Private Sub Command41_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim NewStatus As String
 
If IsNull(DueDate) Then
   Me.Text45 = "No Due Date Entered"
Exit Sub
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblIssue WHERE ID=" & ID)
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
 
    NewStatus = get_Status(RequestDate, DueDate, Status)
    Me.Text45 = NewStatus
 i = i + 1
   rst.MoveNext
 
   Loop
    End If
    rst.Close
Set rst = Nothing
End If
End Sub

 

[plog]

I tried earlier in the thread to get you to mentally seperate the two status entities you have. You have an Actual Status which is what my function calculates, you also have a User Defined Status which goes into that calculation. The User Defined Status is what you are currently storing in your table in the "Status" field.

The status field in your table is now poorly named. To help get your head around it, I would rename it to something like "StatusType" and then refer to the one the function returns as Status. From here on out let's use those terms: StatusType for the field and Status for what the function returns.

StatusType can only have 3 values--Completed, Reviewed or Null. Status is then calculated using the StatusType field (along with the 2 dates). If StatusType is null then the Status will be done using the DateDiff method, if StatusType is not null it will simply return the StatusType as the Status.

Now, for your form: rename the column currently labeled "Status" to "StatusType". The new field you have added you should label "Status". You should set the control source to that last column to this:

=get_Status([DueDate], [RequestDate], [StatusType])


Finally, I would set the Enabled property of that field to False and Locked to True. That will lock the control down and not allow users to click on it--which is perfect because its a totally calculated field.

 

[plog]

Attached is the database sample with the changes I referenced.

 

[steve21nj]

Attached is the database sample with the changes I referenced.

I appreciate the visual and renaming.

The 4th record without a duedate puts #Type!, I don't foresee this ever coming up, but could we add "No Date" if ""

Other than that, I thank you all for the efforts.

 

[plog]

I would catch this in the get_Status() function. Before doing the DateDiff check if its null--if so, set it to whatever status that should have. If not null proceed to calculation.

 

[steve21nj]

Do you mean like this?

I tried with a If Else as well but it still shows #Type!

Public Function get_Status(in_rd As Date, in_dd As Date, in_s)
 
    ' determines Actual status based on request date (in_rd), due date (in_dd) and user supplied status (in_s)
Dim ret As String               ' holds return value
Dim int_DaysDue As Integer      ' will hold difference between due date and request date
 
ret = "Error"
    ' sets default return value to Error
[COLOR=red]If IsNull(in_dd) Then ret = "No Date"[/COLOR]
int_DaysDue = DateDiff("d", in_rd, in_dd)
    ' determines days between due and requested
If (int_DaysDue < 0) Then ret = "Past Due"
If (int_DaysDue = 0) Or (int_DaysDue = 1) Then ret = "Due in 24"
If (int_DaysDue = 2) Or (int_DaysDue = 3) Then ret = "Due in 24-48"
If (int_DaysDue > 3) Then ret = "Due Beyond 48"
 
 ' determines which category int_DaysDue falls into and assigns proper message
If IsNull(in_s) = False Then ret = in_s
    ' if has user supplied status, uses that status as Actual status
get_Status = ret
End Function

 

[plog]

Sort of. It needs have an Else so that it doesn't try and do the DateDiff calculation:

Public Function get_Status(in_rd, in_dd, in_s)
    ' determines Actual status based on request date (in_rd), due date (in_dd) and user supplied status (in_s)

Dim ret As String               ' holds return value
Dim int_DaysDue As Integer      ' will hold difference between due date and request date

ret = "Error"
    ' sets default return value to Error

If IsNull(in_s) = False Then
    ' if has status assigned, uses that one
    ret = in_s
ElseIf IsNull(in_rd) Or IsNull(in_dd) Then
    ' if no status is missing a date value, shows error 
    ret = "No Date"
Else
    ' determines status based on difference between due date and request date
    int_DaysDue = DateDiff("d", in_rd, in_dd)
    If (int_DaysDue < 0) Then ret = "Past Due"
    If (int_DaysDue = 0) Or (int_DaysDue = 1) Then ret = "Due in 24"
    If (int_DaysDue = 2) Or (int_DaysDue = 3) Then ret = "Due in 24-48"
    If (int_DaysDue > 3) Then ret = "Due Beyond 48"
End If

get_Status = ret

End Function

I restructured it so that the logic would go through a if/else flow so it would be assigned correctly. I also removed the 'As Date' assignments in the arguments to allow null values for the dates to pass through and work. Also, I'm with you--I hate VB if/elseif/else statements, so intuitively hard to get right. It took me 5 minutes to figure out correct syntax.

 

[steve21nj]

Awesome. Thank you.

I applied all the principles in my real database, and works perfect.