434,807 Members | 1,492 Online
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

# Calculate date based on Priority

 P: 1 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! Dec 16 '11 #1
5 Replies

Expert Mod 100+
P: 2,321
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.
Attached Files
 DateExample.mdb.zip (14.4 KB, 110 views)
Dec 17 '11 #2

 Expert Mod 100+ P: 2,321 Ok, so now a bit more explanation. I my example database I created 2 tables, with fields: Expand|Select|Wrap|Line Numbers tbl_Priority   KEY_Priority, Number(Long)   lngNrOfDays, Number(Long) Expand|Select|Wrap|Line Numbers 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=tbl_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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. Dec 17 '11 #3

 Expert Mod 15k+ P: 31,489 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 : Expand|Select|Wrap|Line Numbers DateExpected: DateAdd('d', Choose([Priority], 1, 15, 30), [DateReceived]) In the SQL this would look like : Expand|Select|Wrap|Line Numbers DateAdd('d', Choose([Priority], 1, 15, 30), [DateReceived]) AS [DateExpected] Dec 17 '11 #4

 Expert Mod 10K+ P: 12,366 A third option would be to add Expand|Select|Wrap|Line Numbers 15 * (Priority - 1) + 1 \ Priority Dec 18 '11 #5

 Expert Mod 15k+ P: 31,489 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 Dec 18 '11 #6