Adding number of months to a date

[grego9]

I am trying to add the number of months as referenced in my "profile months" field (in my FERC DATABASE2 table) to the date in my Tender Expiry field of the same table


I have seen the DateAdd function

where
DateAdd(Interval,Number,Field)

This works fine if I put
DateAdd("m",1,],[FERC DATABASE2]![Tender Expiry])

...this adds one month to the tender expiry field.

My problem is that I don't have a fixed number of months that I want to add to the tender expiry field. I want to add the number of months as detailed in the profile months field (which contains numbers 1-12)

So I entererd the following...

DateAdd("m",[FERC DATABASE2]![Profile1Mths],[FERC DATABASE2]![Tender Expiry])

I just get an error in the calcualtion field - any ideas?

Any help would be greatly appreciated!

 

[boblarson]

You can't refer to a table like that. You need to use a DLookup to get info out of the table.

 

[grego9]

I amended the expression to read

Expr2: DateAdd("m",DLookUp("Profile1Mths","FERC DATABASE2"),DLookUp("Tender Expiry","FERC DATABASE2"))

and this errored

I also tried

Expr2: DateAdd("m",DLookUp("Profile1Mths","FERC DATABASE2"),[FERC DATABASE2].Tender Expiry)

and this failed

any ideas?

thanks

 

[grego9]

think I missed the square brackets off.

Expr2: DateAdd("m",DLookUp("[Profile1Mths]","FERC DATABASE2"),DLookUp("[Tender Expiry]","FERC DATABASE2"))

I know get a data mismatch.

The tender expiry field is a date field and the profile months field is a number - but that should be fine as I want to add the number of months in the profile months field to the tender expiry date using the DateAdd function

Any ideas?

 

[Brianwarnock]

If you are doing this in a query and it looks as if you are then

Expr2: DateAdd("m",[Profile1Mths],[Tender Expiry])

should work, infact I don't understand why your last example in post1 didn't work if that was in the query on which the form is based.

Brian

 

[grego9]

Brian - I was perplexed myself! Fortunately it took you suggesting that it should work for me to double check. I re-ran the query and got error messages - but on closer inspection these errors are only against the rows where the profile1Months is blank.

Problem solved - thanks for your help

 

[Brianwarnock]

Good, happy to have helped, and yes I was puzzled.

Brian