Calculate date based on Priority

[Bruce Morris]

I'm wanting to calculate a date in a field based on a Work Order Priority. Example, return a date from date recieved based on Priority 1, 2 or 3. Priority due in 1 day, 2 due in 15 days 3 due in 30 days. I,m a very basic user!

[TheSmileyCoder]

Hi and welcome to Bytes.

First, its nice that you state that your a basic user, it does make it easier to formulate the correct level of detail to supply in the answer.

If you are just starting database development I would suggest you check out this article:
(Database Normalisation and Table structures).
A good understanding of this concept is critial for any good database design.

Now this particular question is one where you can consider whether to go for a fully normalized design, in which you don't actually store the calculated date, but calculate it each time it is needed. A reason not to go for the fully normalized design is that it will be easier to make changes to the amount of days to add at a later date if we dont go for the normalized approach.

The following is based on a design that is not fully normalized as I believe its the solution based on what you have told me.


I have made a small example database, and attached it. I was going to explain in more detail the things I did, but got a hyperactive kid atm.

[TheSmileyCoder]

Ok, so now a bit more explanation. I my example database I created 2 tables, with fields:

tbl_Priority
  KEY_Priority, Number(Long)
  lngNrOfDays, Number(Long)

tbl_Example
  KEY_Example, Autonumber
  tx_Example,  Text(50)
  lng_Priority  Number,Long
  dt_Received  Date/Time, Default Value "=Date()"
  dt_Due       Date/Time

I made a form frm_Example, with Recordsource=tb l_Example.
I placed all the fields into the form (Thus creating controls), then renamed the controls. For the Key field, I Disabled it (since its autonumber and thus noone really needs to be able to enter it for edits).

I made a combobox for the lng_Priority field, and named it cmb_Priority. I gave it the rowsource:

SELECT tbl_PriorityDays.KEY_Priority AS Priority, [KEY_Priority] & " - " & [lngNrOfDays] & " Day(s)" AS Expr1, tbl_PriorityDays.lngNrOfDays FROM tbl_PriorityDays;

I then set the Columncount for the control to 3, and columnwidths to:

0cm;3cm;0cm

This means the user does not see the first column, nor the 3rd column, only the second column which illustrates to the user both the priority value, as well as the number of days associated with that value.

I set the Validation rule for both the combobox cmb_Priority and tb_DateReceived to Is Not Null, and set the validation text "This field cannot be left empty". That message will be displayed if the user tries to clear either of the fields.

I then made a piece of code for the afterupdate event of both the cmb_Priority as well as the tb_DateReceived . To start the code, select either of the two controls, look at its properties and go to the event tab. Find the AfterUpdate property and click the builder icon (The ... on the right hand side) Select Event Procedure to start writing some code.

The code for both events is here:

Private Sub cmb_Priority_AfterUpdate()
    Call setDueDate
End Sub

Private Sub dt_Received_AfterUpdate()
    Call setDueDate
End Sub

As you can see they both call the same piece of code, placed int he same module. The setDueDate code is shown below:

Private Sub setDueDate()
    'There are validation rules to prevent the tb_ReceivedDate being empty and prevent the cmb_Priority being empty
    ' Therefore we dont need to check for that, however I will add the code anyways, in case your design is different
    If IsNull(Me.cmb_Priority) Or IsNull(Me.tb_DateReceived) Then
        'Cannot perform calculation, so exit
        Exit Sub
    End If
    Me.tb_DateDue = DateAdd("d", Me.cmb_Priority.Column(2), Me.tb_DateReceived)
End Sub

This will lift the number of days from the combox (see the Me.cmb_Priority .Column(2), and remeber that the first column is column 0), and calculate the due date.


Hope that was sufficient explanation, otherwise you are of course welcome to return and ask for more advice.

[NeoPa]

If you have one field called [Priority] and another called [DateReceived] (note spelling) then, assuming both are always populated with valid values (1, 2 or 3 for [Priority] and a valid date for [DateReceived]), the formula to return a field called [DateExpected] in a query would be :

DateExpected: DateAdd('d', Choose([Priority], 1, 15, 30), [DateReceived])

In the SQL this would look like :

DateAdd('d', Choose([Priority], 1, 15, 30), [DateReceived]) AS [DateExpected]

[Rabbit]

A third option would be to add

15 * (Priority - 1) + 1 \ Priority

[NeoPa]

That would result in 1, 15.5 & 30.33... surely?

PS. No. I should have realised that you wouldn't make such an obvious mistake. It's a horrible solution that you should be ashamed of, but obviously is perfectly correct. Love it :-D