Elapsed Time Frame

[dsellers1]

Well, sort of. So here's what I'm needing to do:

In my database, I have a "Time_Frame" table that houses 3 abbreviations along with each of their corresponding time frames (45, 30, 15 days). This table is used for a combo box on the form entitled "Results". What I am wanting to do is while working in the "Results" Form, I enter my original date and then select the abbreviation from the drop down. From there the end date will populate based on the entry from my original date plus the time frame from my time frame table.

I think I should go with a query, but then I think VB is the way to go. Either way, I am drawing a blank on how to do it. Any help would be greatly appreciated. Attached is a quickly thrown together example

 

[jzwp22]

From there the end date will populate based on the entry from my original date plus the time frame from my time frame table

You would use a calculated control on your form, but in most cases you would not store the calculated value in the table.

The expression would go something like this:

=dateadd("d", nameofcomboboxcontrol.column(x), [name of control holding original date])

where x corresponds to the column that holds the time frame. Access starts counting the columns at 0 not 1.

 

[dsellers1]

Thanks for the input jzwp22. In most cases, you are right, there isn't a need to store this value in a table but I need it for this one due to reporting purposes. I tried your suggestion but it doesn't seem to update the End Date field. I still think your expression is correct, I just have to figure out how to incorporate it into the way I need it. Thanks again!

 

[jzwp22]

If you store the original date and the time frame in your results table there is no need to store the calculated value as well. If you need the calculated value on a report, you can use a query that does the calculation (the query would need to join your results table and the time frame table) and base the report on that query.

Technically speaking you can use some code in the after update event of the combo box that does the calculation (using the same expression) and assign it to the control for the end date. However, if someone changes the original date without reselecting something from the combo box, the end date would not update to reflect the original date. Then you would have a data integrity issue which is why storing calculated values in a table is not a good idea.

 

[dsellers1]

You're right. Why I didn't think of doing the calculations at the end, who knows? Thinking about it, I don't need that data to populate on the form but on the final report. I went in and through together a quick expressions query and lo and behold, it worked. Thanks again for the input!

 

[jzwp22]

Glad to hear that you reached a solution. As an alternate solution, you could have the report's query bring in the original date and the time frame value and let the report do the end date calculation.