OnTime field code using DueDate

[Cadmesh]

Hello to you all,

I am almost at the end of my database but there are still a couple of things I need to know. My tables and forms are all setup.
My DB tracks and registers Technical Documents (TD) (or just documents). These TD's are created by an employee and are the send to different departments.
Now, I have a couple of fields giving the specifications of the TD's (ordernumber, title, author..).
I (also) have a column/field in my main table to let me know if a TD has been finished. The field is called Final with Yes/No format.
I have a field to let me know the date the document is expected to be completed. Field is called DueDate, with date format.

Now, I would like a field that let's me know if the document is OnTime.
I have to write some simple code for this, making use of the fields: Final (Yes/NO), DueDate (Date) and Current (Date). This is where my problem lies as Im not good at writing code.

But technically it has to be something like this (I think).

OnTime=No, if Final=No and (at the same time) CurrentDate>Duedate. Otherwise OnTime=Yes

If one of you guys could help me write this as it should be, I would really appreciate it.
Oww by the way I realize that I have to create this field in a query (right? What kind of query?)
Thanks in advanced for your help.

 

[Cadmesh]

Hello to you all,

I am almost at the end of my database but there are still a couple of things I need to know. My tables and forms are all setup.
My DB tracks and registers Technical Documents (TD) (or just documents). These TD's are created by an employee and are the send to different departments.
Now, I have a couple of fields giving the specifications of the TD's (ordernumber, title, author..).
I (also) have a column/field in my main table to let me know if a TD has been finished. The field is called Final with Yes/No format.
I have a field to let me know the date the document is expected to be completed. Field is called DueDate, with date format.

Now, I would like a field that let's me know if the document is OnTime.
I have to write some simple code for this, making use of the fields: Final (Yes/NO), DueDate (Date) and Current (Date). This is where my problem lies as Im not good at writing code.

But technically it has to be something like this (I think).

OnTime=No, if Final=No and (at the same time) CurrentDate>Duedate. Otherwise OnTime=Yes

If one of you guys could help me write this as it should be, I would really appreciate it.
Oww by the way I realize that I have to create this field in a query (right? What kind of query?)
Thanks in advanced for your help.

 

[Cadmesh]

Now Im not completely sure if I have to do this in a query. Is it possible to write this code at the Validation Rule property of the field itself? The question is still, what is the code.
Cheers

 

[dbDamo]

Something along the lines of:-

If Me.Final = "No" Then
    If Me.CurrentDate > Me.DueDate Then
        Me.OnTime = "No"
        Else
        Me.OnTime = "Yes"
    End If
End If

The problem is deciding where to place the code to set the value of the OnTime control. I am assuming that the CurrentDate control is unbound and simply set to = Date(). I am also assuming that the DueDate control is calculated when the record is added (using a calculation like CurrentDate + x days). This means that the only logical place for this code in in the Final control's AfterUpdate event.

As for validation, you can either write some code in the form's BeforeUpdate event and your save button's OnClick event or lock the OnTime control at the end of the code above, so thast the user cannot overtype the value. For the 2nd option, the above code would then be:-

If Me.Final = "No" Then
    If Me.CurrentDate > Me.DueDate Then
        Me.OnTime = "No"
        Me.OnTime.Enabled = False
        Else
        Me.OnTime = "Yes"
        me.OnTime.Enabled = False
    End If
End If

 

[Cadmesh]

Thanks for your replay Damo, your assumpitions were correct. And yes the double post was because at the moment I was posting my pc went down (so didnt know if I had posted it or not hehe).
I decided that maybe it was better to change the color of the DueDate field if a document is not on time. I am in the process of solving this with conditional formatting, I think I'll manage...I'll let the world know if I've got more questions. Cheers