Query or Function to Update another field value

[steve21nj]

Main question: Would I use an update query or function to complete my task?

Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]

Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.

[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due

Query: Below is the query I just started to ignore the calculation if either “Review” or “Completed” exist.

Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))
If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.

Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)

Am I going about this wrong?

If I am in the right direction, can someone help me on the next step.

Thank you.

 

[plog]

In the words of Homer Simpson, you're so close but completely wrong.

Status should not be a value you store in a table. It should only be a field in a query that you calculate. When you want to get the status, you refer to the query that calculates it.

Now to accomplish this, you're on the right track. I would create a function, and pass it the 2 dates you are using to determine it. In a query the function call would look like this:

Status: get_Status([RequestDate], [DueDate])

It would do the datediff function you mentioned and then return whichever stratification it falls into.

So, the next step is to start on that function. It sounds like you know what you are doing so I'll leave that to you, but if you need help, just post what you have and I'll be glad to help.

 

[steve21nj]

Thank you for the response. The only reason I was storing the value was because of the manual selections on either "Review" or "Completed". A user would have to determine this, rather than a query. But you are saying run my Expr1 in the function, looking for either of the manual selections above and updating the others?

The database will be rather small, maybe 500 entries at most per year. So I was trying not to over complicate it, staying on the edge of Normalization.

 

[plog]

I just read that the user can actually assign a status. This doesn't significantly effect my answer.

You still need to calculate the Status (lets call it Actual Status so not to get the two confused). You just need to take into account a user supplied status, which means passing the function the [Status] field.

ActualStatus: get_Status([Status], [RequestDate], [DueDate])

Now, you need to limit what is in the [Status] field. It can only contain null, 'Review' or 'Completed', not the others. When the data gets to your function it will only run the calculation if the [Status] is null. Otherwise it will return the [Status] it receives.

 

[steve21nj]

I am horrible with functions, and I cannot wrap my mind around this. Could you assist.

Option Compare Database
Public Function get_Status(RequestDate As Date, DueDate As Date) As Boolean
 
    On Error GoTo Err_Execute
Set db = CurrentDb()
Set rstsource = db.OpenRecordset("tblIssues", dbOpenDynaset)
Dim varDate As Variant
 
If [Status] = "Completed" Or "Review" Then Exit Function ' No paramters
varDate = DateDiff("d", [RequestDate], [DueDate])
If varDate >= -1 Then
'do something? Status to Past Due
If varDate > 0 Then
'do something? Status to Due in 24
If varDate > 1 Then
'do something? Status to Due in 24-48
If varDate > 2 Then
'do something? Status to Beyond 48
End If
Loop
'go to next record?
 
 Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    get_Status = ""
    Call MsgBox("An error occurred while trying to determine the issue status." & Chr(10) & Err.Description, vbCritical)
End Function

 

[plog]

This is 95% of the way there:

Public Function get_Status(in_rd AS Date, in_dd As Date, in_s As String)
    ' 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

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

You still need more If statements to check for all status possibilities.

 

[steve21nj]

This is great. I added the additional values at the bottom.

If the value is null, Id like "Enter Date" to the value.

Question: you mentioned earlier to not store the values, which i normally dont, but because the user defines the value in some cases, I should continue to store it, right? Where would I reference this code in my query from above?
Also, if the values are stored, and I open the query, will the values be automatically updated say when i login the following day until the user updates to completed?

Public Function get_Status(in_rd AS Date, in_dd As Date, in_s As String)
    ' 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

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 = "Enter Date"
    ' if has user supplied status, uses that status as Actual status

get_Status = ret

End Function

 

[plog]

You should continue to store the user inputted status, but remember its just part of what goes into the Actual Status.

Right now you said you have a field in a table called "Status" that can have 1 of 6 values (4 calculated and 2 user inputted). I propose you change it so it can have 3 values: Null, "Review", "Completed". Then to determine the Actual Status you use the function.

If you look at the first line of it, you will see it takes 3 arguments--you need to pass it the due date, the request date and the status. If the status is Null it does the math on the dates and determines it that way, if it has data it just returns the status that's in the status field (the user supplied one).

 

[steve21nj]

Thank you for the feedback. I decided to create a sample db to test the information until I return back to work.

When attempting to call the function i get a Compile Error, Argument not optional.

Based on the sample database provided (both formats), can you point me in the right direction.

 

[plog]

You have to pass get_Status() data. You can't just type "get_Status"; it requires you pass it data.

 

[steve21nj]

Passing the var I would just put it like this, no?

Private Sub Command40_Click()
 
Call get_Status(RequestDate, DueDate, Status)
 
End Sub

When I do, it keeps saying Invalid Use of Null. If I manually give each a status, no error.

 

[plog]

Ha ha, this is why I help out here--so I can learn. By telling the function in_s was a string it screwed it up when nothing came thru (Null). So by removing the declaration that in_s is a string it will work. Replace the first line of the function with this:


Public Function get_Status(in_rd AS Date, in_dd As Date, in_s)

 

[steve21nj]

That eliminated the error but nothing is happening. Based on the code, what will happen if [DueDate] is blank?

Here is the coding and attached database.

Function:

Option Compare Database
Option Explicit
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
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

Command Click

Option Compare Database
Option Explicit
 
Private Sub Command40_Click()
 
Call get_Status(RequestDate, DueDate, Status)
 
End Sub

 

[CazB]

2 issues - first, although you were telling it to call the function, you weren't then telling it to do anything with it!
Second - I've added in a check so that it doesn't do anything if there's no DueDate
Try changing the code on the OnClick of the button as follows

Private Sub Command40_Click()
Dim NewStatus As String
    If IsNull(DueDate) Then
 
        MsgBox "Cannot calculate Status, no Due Date Suppplied", , "Missing Date"
        Exit Sub
    Else
    NewStatus = get_Status(RequestDate, DueDate, Status)
    Me.Status = NewStatus 
 
    End If
 
End Sub

NB
you could also just assign it straight to the field, using me.status=get_Status(RequestDate, DueDate, Status) but I prefer to assign it to a variable first so I can check it!

 

[steve21nj]

CazB,

Thank you for the response, and that makes sense. Three things.

1. I want it to loop through all the records, not just the current. I created the test database/button for example purposes. I'd really like to launch on form load/current. Is this possible to loop through all records?
2. Instead of the msgbox, I'd simply like "No Date" to appear in the box.

****3. So testing the data, based on what i have, i decided to change a duedate in the first record to a later time. Since it has a status [Due in 24], it will not update to new status [Beyond 48]. In my original query, I had it search for "Completed" or "Review", if neither were present, it would do the calculation. But now it writes the value to the record and wont update.

[FONT=Calibri]Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))[/FONT]

 

[CazB]

It can be done, but I'm afraid I'm no good at looping things, so someone else will have to help you with that one
As for replacing the msgbox, just put in

Private Sub Command40_Click()
Dim NewStatus As String
    If IsNull(DueDate) Then
         me.status="No Date"  
      Exit Sub
    Else
    NewStatus = get_Status(RequestDate, DueDate, Status)
    Me.Status = NewStatus 
 
    End If
 
End Sub

 

[plog]

No, no no. We've been over this--you should not store the value you get back from get_Status. You should instead call the function whenever you want the actual status of a record.

If you want the status to appear on your form you should create an unbound control and set its control source to the function, passing it the values from the row it is in.

 

[CazB]

my bad, didn't read the whole thread - sorry (put it down to being full of cold ) I'll back off!

 

[steve21nj]

No, no no. We've been over this--you should not store the value you get back from get_Status. You should instead call the function whenever you want the actual status of a record.

If you want the status to appear on your form you should create an unbound control and set its control source to the function, passing it the values from the row it is in.

I'm sorry for asking so many questions, but I'm trying to think of how the user will interact with this.

So you want me to change Me.Status to my unbound Me.Text45?
If I do this on my display form, it changes every record to that update. And if the user wants to update to "Completed", how would it pass the value to Status?

I'm a visual person, I can't take your words, as simple as it sounds, and apply it at the moment.

 

[David R]

You store the DueDate. You CALCULATE the Status.