Updating one date field based on another

[barryg80]

Hi folks,



I am a newbie to Access and so struggling with something that I'm sure should be more straight-forward than I am making it.

I have a simple database of 2 tables. Clients table and Tax Returns table.

I am trying to add some automation to the form used for inputting the tax return info by updating the value of one date with to be 28 days after another. So [Field_B]=[Field_A]+28. How to use this formula is my basic dilemma.

Initially I tried setting the default value of Field_B but discovered you cant do it like that. Then I started looking at calcuated queries and i can get this to create a new field in the table with the updated dates in it but I cant work out how to put this on the form?

Lastly I tried to go into the properties of Field B on the form by setting the default value there but it doesnt seem to update the form/table?

Where am I going wrong here?!

Many thanks in advance

[rpeare]

You don't want to store calculated values in your table, ever, it's a really bad practice.

If you are looking for a tickler (let's say you want a reminder in 28 days to contact someone) then in a query you'd have an additional field that was something like:

ReminderDate: dateadd("d", 28, [Field_A])

then in the criteria of your reminderdate field you could have something like
=date()

where you're looking for any reminders for today's date

[barryg80]

You don't want to store calculated values in your table, ever, it's a really bad practice.

If you are looking for a tickler (let's say you want a reminder in 28 days to contact someone) then in a query you'd have an additional field that was something like:

ReminderDate: dateadd("d", 28, [Field_A])

then in the criteria of your reminderdate field you could have something like
=date()

where you're looking for any reminders for today's date

Thanks for getting involved rpeare.

So you are saying what I am trying to do isnt really advisable? I have written some reports for the users to show them upcoming important dates based on the data, i am just looking for a way to cut down on the data entry by calculating this value as there will be quite a few of them. Should I just run the calculate query on the DB and then copy the calculated dates into Field_B and give it to users like that?

[rpeare]

No, I'm saying if you have a series of calculated values that can all be figured from a smaller dataset there is no reason to store them at all. On any given record with your minimum data set you can calculate the values you want for the purposes of displaying on a form, report or query.

So let's say you have ORDERDATE on your Main form.

From here (just as an example) let's say your ship date is always 7 days after your order date and your invoice date is always 30 days after your order date.

On your form during data entry once they put in 1/1/2013 as their order date you can have 2 unbound controls on your form

=orderdate + 7
=orderdate + 30

any time orderdate is populated these fields will be as well but they will not be stored.

Then when you build a query to see what items have to ship on a given date you'd have in your query

ShipDate: Orderdate + 7 (or dateadd("d", 7, orderdate)

then in your criteria you'd have

date()

so it would only show items that are supposed to ship today

[barryg80]

Thank you rpeare thats a great explanation and I see now the direction I need to be going. Many thanks