By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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
File Type: 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
  1. tbl_Priority
  2.   KEY_Priority, Number(Long)
  3.   lngNrOfDays, Number(Long)
Expand|Select|Wrap|Line Numbers
  1. tbl_Example
  2.   KEY_Example, Autonumber
  3.   tx_Example,  Text(50)
  4.   lng_Priority  Number,Long
  5.   dt_Received  Date/Time, Default Value "=Date()"
  6.   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
  1. 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
  1. 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
  1. Private Sub cmb_Priority_AfterUpdate()
  2.     Call setDueDate
  3. End Sub
  5. Private Sub dt_Received_AfterUpdate()
  6.     Call setDueDate
  7. 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
  1. Private Sub setDueDate()
  2.     'There are validation rules to prevent the tb_ReceivedDate being empty and prevent the cmb_Priority being empty
  3.     ' Therefore we dont need to check for that, however I will add the code anyways, in case your design is different
  4.     If IsNull(Me.cmb_Priority) Or IsNull(Me.tb_DateReceived) Then
  5.         'Cannot perform calculation, so exit
  6.         Exit Sub
  7.     End If
  8.     Me.tb_DateDue = DateAdd("d", Me.cmb_Priority.Column(2), Me.tb_DateReceived)
  9. 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
  1. DateExpected: DateAdd('d', Choose([Priority], 1, 15, 30), [DateReceived])
In the SQL this would look like :
Expand|Select|Wrap|Line Numbers
  1. 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
  1. 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

Post your reply

Sign in to post your reply or Sign up for a free account.