Calculating Dates

[PMOB]

I’m having trouble calculating a date field and was hoping someone could assist me.

I have two bounds fields on a form – both fields are a “medium date” format both in the table design and the form view properties.

Field #1 = is a user entered date
I want Field #2 to be = Field #1 plus 18 months (i.e. 547 days)

In the form properties for Field #1 I have the “AfterUpdate” option set to run a macro.

The macro uses the SETVALUE option as follows:

Item [Field #2]
Expression =[Field #1]+547

Now in theory when Field #1 is updated, the macro should add 547 days to Field #1 and put that new date into Field #2. However, the only date it returns in Dec-30-1899 – which I know is the “zero” date for how Access calculates dates.

What am I doing wrong?

Any assistance will be much appreciated.

 

[ajetrumpet]

Field #1 = is a user entered date
I want Field #2 to be = Field #1 plus 18 months (i.e. 547 days)
The macro uses the SETVALUE option as follows:

Item [Field #2]
Expression =[Field #1]+547

However, the only date it returns in Dec-30-1899 – which I know is the “zero” date for how Access calculates dates.

It is probably because you are adding a date to a number. I'm suprised you didn't get an error of somekind. Does the expression line in the macro action actually READ [Field 1] + 547??

If it does, you are adding a number to Field 1, not a time interval. The expression should read...

DateAdd("mm", 18, Forms![FormName]![Field1ControlName])

Also, if you need to be adding 18 months, I would use MONTHS and not days in the function, as 18 mos does not always equal 547 days from year to year.

 

[Dennisk]

you can add numbers to dates as a date is a floating point number however 18 months is not 547 days as it depends on the number of days per month. The dateadd function is the way to go.

 

[PMOB]

AJ - I tried your format but it still didn't work but I ended up developing a work-around for the problem.

Since I can get this to work via an unbound text box I use on one the form to display the correct date. The unbound text box control is set to = [FIELD 1] + 547.

I then set the AFTER UPDATE property of FIELD 1 to run a maco which runs an update query which then places the proper date in the field in the table.

For some reason my syntax of [FIELD 1]+547 works with the unbound text box on the form as well as in the update macro - the only place I can't get it to work is with the bound field on the form.

Oh well, no matter - the form looks like it should and the table has the necessary date in the correct field, the maco runs in the background with the warnings off so its invisible to the user - in the end no harm, no foul.

Also - I know that the 547 isn't technically, exactly 18 months but for my purposes it is close enough.

Thanks to all who responded.