automatically change expiration date's fields record after one yea

S

SCB

I have an "expiry date" field that has a default value of: Date()+365, so it
calculates the expiry date automatically. Now I also want this date to change
every year automatically.
Please help...
 
K

Klatuu

First, Date() = 365 will work until you hit a leap year, then it will loose a
day. You should always use date math functions when working with dates. For
example. If you use 5/1/1999 as you date, then your math will return
4/30/2000. Using the corrrect function, it will return 5/1/2000 as it should.

DateAdd("yyyy", 1, date)

Now, to change the dates, you will need an update query that will use the
above function in the Update To of the query builder.
 
S

SCB

Can you please explain how to do this, this is somewhat new for me. I
couldn't figure out how to do it.

Please help again. and THANKS for the reply
 
K

Klatuu

Create a query using the query builder. Unless there is some filtering
required, you only need to include in the query the date field you want to
change. Select Update as the query type. In the row of the query builder
that says Update To: enter the code I posted previously. When you run the
query, all records will be updated with the new value.
 
S

SCB

It works!
But now I've noticed. It will change all records. Can it only changes the
records I filter?
 
K

Klatuu

Yes it can. You would include the fields you want to filter on and put the
filtering criteria in the Criteria: row. Leave the Update To: row blank so
it will not change those fields.
 
S

SCB

Thanks a lot, everuthing is working.
I just had one more question, I hope you can help me again.

I have made the update to query. I can also filter the dates I want it to
change.
I have made a macro to run the update query-change the expiration date by
adding one year-and to open a report to show the expiration dates.
Problem is the to report that opens I have also a filter for the exp.
dates,and it asks me to enter the filter criteria.
I wanted it to open the report with the dates that I have already filtered
when running the update to query.
Is this possible?

I hope to have explained myself, it's difficult.

I hope you can help me.

Thanks again!
 
K

Klatuu

I don't know where you are applying the filter for the report. It can be
either in the query that is the report's recordset or in can be in the report
itself. The best way to do what you want is to create a from where you enter
the dates, then use the form to run both the update query and the report.
Both queries (I am assuming you are filtering the report in the query) should
then reference the form to get the dates. So the criteria for each date
shoud reference the form something line:
[Forms]![YourFormName]![FormControlName]
 
S

SCB

Thank You again! You've helpt me again.

Have a nice day!

Klatuu said:
I don't know where you are applying the filter for the report. It can be
either in the query that is the report's recordset or in can be in the report
itself. The best way to do what you want is to create a from where you enter
the dates, then use the form to run both the update query and the report.
Both queries (I am assuming you are filtering the report in the query) should
then reference the form to get the dates. So the criteria for each date
shoud reference the form something line:
[Forms]![YourFormName]![FormControlName]

SCB said:
Thanks a lot, everuthing is working.
I just had one more question, I hope you can help me again.

I have made the update to query. I can also filter the dates I want it to
change.
I have made a macro to run the update query-change the expiration date by
adding one year-and to open a report to show the expiration dates.
Problem is the to report that opens I have also a filter for the exp.
dates,and it asks me to enter the filter criteria.
I wanted it to open the report with the dates that I have already filtered
when running the update to query.
Is this possible?

I hope to have explained myself, it's difficult.

I hope you can help me.

Thanks again!
Click to expand...
Click to expand...
 
Top