Expression Builder in Data Macro

[Korreia]

Hi All,

I'm facing an issue when I'm creating a Data Macro After Insert in Access 2010. In the value for the SetField, I use the expression builder. I wanted to use the DateAdd function but it not showing, also I cannot see my user define function (Module).

This behaviour seem to occur only in the instance of Expression Builder while in the Data Macro, everything is fine anywhere else including macro.

Any idea?

[June7]

I tested ExpressionBuilder from the event property as well as within the macro. I do see DateAdd function under the Intrinsic-Date/Time category. I also see my custom function under the module functions. Can't replicate the issue.

[Korreia]

Thanks for the reply

This issue only occur while in the Data Macro. While in Macro, the Expression Builder shows the DateAdd and my module Function. Not sure which version of Access you've tested this, however Data Macro is a new feature of Access 2010. Must be on Table Design to be able to set Data Macro triggers After Update or other event.

[June7]

I get it now. Have not used DataMacros yet. I guess that's the way it is then. Some functions not available to DataMacros.

[Korreia]

Any idea how I could DataAdd or achieve the same with other function? I need to add 3 month to a date in another table (tblRenewals) After Insert in tblMembers. That why I want to use DataMacro. I know I could do it via a form, however I want to do it a table level since a user could go directly in the tblMembers and add a new row without adding 3 month to the date in tblrenewals. I try using this Month(Now()+3), it work for the month but the day and year is not right...

[June7]

Why do you need to save calculated data? Can't you just calculate when needed? Is DateSerial available to DataMacros? (Don't have 2010 available at the moment)

DateSerial(Year(Date()), Month(Date())+3, Day(Date()))

or

CDate(Month(Date())+3 & "/" & Day(Date()) & "/" & Year(Date()))