Fill in a field based on 2 others (1 Viewer)

Hi can you help?

I have 3 field in a table
Opened date / Status / Target date

There are 4 status Critical , Major, Minor & low

I want the Target date field to autofill
If status is critical the target date is opened date plus 1 day
If status is major the target date is opened date plus 2 days
If status is minor the target date is opened date plus 14 days
If status is low the target date is opened date plus 28 day

If tried using setting the Target Field as a calculation and using an IIF function but gets errors.


Can you suggest how to do this please.
I am a novice user, learning from youtube and google.

Hello TSM, and again, welcome to AWF.

It is bad practice to store calculated values in a table. As you alluded to in your introduction, this is a habit of Excel users which you have e no love for.

It is best to do what you are trying to do with forms or reports. Were you planning on doing any for your project?

Yes I've done a few basic forms and reports.
I have absolutely no idea how to calculate the Target date based on the other 2 fields in a report.
I'm guessing I would need to set up a query? The queries I've done so far have been basic just lists really.

You do not need a query, you can use the built in expression builder. Here is a link to a YouTube video to help you get started.

https://youtu.be/xzPRelBOxiQ

Thank you, I'll take a look. Although it maybe tomorrow now.
Thanks

You claim to be a novice so I will aim this answer at that level.

First important thing to know relevant to your question: Queries are your friend.

A calculated field is a big no-no in a table but perfectly well suited for a query. However, if you must store the calculated value, then a query is not the answer.

Ideally, if you must store the date, perhaps because it is subject to manual update, then the best way (in my not-so-humble opinion) is that you build a form to store the record and that you learn a little bit about VBA so that when you store the opening date and the status level, you compute and fill in the target date. Once those two items are defined, the third item is also defined. Therefore, compute it and store it.

The problem with doing this with, say, the default value property for the field is that at the time the record is created, it is still blank, so the default value computations get nulls or blanks for input and don't have the desired effect.

Tables don't normally have a mechanism for filling in that blanks like that, so you can't fit a computation there.

Queries can do this with a SWITCH clause or a (less preferable) nested IIF clause, but doing it that way inhibits or removes your ability to update the field if you ever needed to do so. Not saying you WOULD, but this approach at the query levels is just a stopper.

Doing it via forms gives you a venue for complex or simple VBA or anything in between, so you have the widest possible range of options. Putting some VBA code in the Lost Focus events of the Status and Open Data gives you the chance to test whether the date has been entered already and, if not, gives you the chance to compute the date.

As a side note, when designing databases, watch out for spaces in names. They force you to enclose things in brackets. Like [Opened Date] vs OpenedDate and [Target Date] vs. TargetDate. It's not that Access can't handle spaces like that when done properly, but it is a pain in the toches to do it properly.

As Doc has alluded to, in the design of your system is [Target Date] ALWAYS a set number of days after the [Opened Date] (based on [Status]), is it based on a business schedule (Monday to Friday for example), or is it user editable?

Each has a different solution, but you would need to be very clear on the design specification and if there is EVER a chance this would be excepted.

I agree with the others. Calculated data should rarely be stored. Never is too big a position and Doc mentioned one of the benefits of storing it.

However, given your criteria for the calculation, I would store that in a table. That will make the calculation simple and easily changeable should the number of days for an option need to change or a new option need to be added.

critical,1
major,2
minor,14
low,28

Then to perform the calculation, your query would join to the status table using a left join if status is optional otherwise an inner join. The calculation then becomes

OpenedDate + Days

No logic is needed.