Calculate a date based on dropdown

[robojeebus]

I have a drop down box in my access database which I want to use to give completion dates to jobs, the drop down has three values:

Emergency
Urgent
Routine

emergency equates to 1 day
Urgent 7days
Routine 28days

I have had a dig in the help files and I can get the order completion date to show the terms emrgency, urgent and routine, however I nned to calculate the date, not just show the term, this where I lose track of how to get any further, I have searched the forum and cannot find an example, but perhaps I am not using the right search criteria, I have tried date calculation on drop down results etc etc and no dice, I am sorry if I am posting a question whichg has already been answered, and if anyone can post a link to a solution that would be great to.

Many thanks for your help this is my first post on this forum, but I am sure I will be here for a long tme to come access is fast becoming my life

again many thanks for taking the time to read this and I appreciate any help you can give me.

 

[antomack]

You should calculate the completion date in a query or direct on a form rather than storing in the table. Create a query and add a field as below to get your result
CompDate:IIf([state]="Emergency",[orderdate]+1,IIf([state]="Urgent",[orderdate]+7,[orderdate]+28))

 

[neileg]

antomack's suggestion should work. I would do it slightly differently. I'd hold the time periods in a table where you hold the data for the drop down. In the drop down, add the period as a second column, but set its width to zero so the user can't see it. Then your date calculation simple requires you to add the number in the hidden column to the order date. This way, you can add new options or vary the time periods without having to hack your code.

 

[neileg]

Aaaagh, you double posted!