date calculation then autofill?

[nktrygg]

Hello



Just wondering if there's a way to do this:

field 1 - manually fill in a date

feild 2 - refers to field one, subtracts a year and then automatically fills in that date.


I have a databased that manages 400 records that are leases/legal agreements - and field 1 is the expiry date of the agreement and feild 2 is the date we have to give notice to the leasee that the contract is going to expire

I know it might seem pedantic to have the field 2, but I have to run a BF report for an 18 month period to let other people know that they have to give notifications ahead of time.


So, the report I give them will have

lease # / client / expiry date / expiry notice

[llkhoutx]

Do not use blanks or special characters in field name. It will cause a world of grief when building SQL queries.

field1 populated on a form

me.field2=dateadd("yyyy",-1,cdate(field1))

[jbarrum]

Create a date field in your table and in the "default Value" parameter put the following:

Date()

This will default the date in the record to the system date when the record is created.

Next you can creat a query from that table and add a column with the following expression:

NewDate: [DateFieldName]-365

This will look at the date field and subtract 365 days and return a new date one year earlier.

[llkhoutx]

Subtracting 365 is just not right. Decrement the year as I posted.

[jbarrum]

I suppose that leap year would pose a problem with my method?

[nktrygg]

I suppose that leap year would pose a problem with my method?

the date the record is created isn't relevant, usually the record is added to the database well in advance of the agreement being active - it's entered and tracked as a pending agreement

it's the start date, event dates (ie: rent review) and end date of the agreement

but, that will help for the other BF feild where I have to enter a date and then a 26 weeks notification date - which is a set number of days - so it does help me too

[nktrygg]

Do not use blanks or special characters in field name. It will cause a world of grief when building SQL queries.

field1 populated on a form

me.field2=dateadd("yyyy",-1,cdate(field1))

thank you!