asked on

Access 2013 Calculated Date Columns in Table

I have an access table that I'm trying to append to a SharePoint list. The SP list has two calculated columns:
CurrentMonth: IIf(Str([Today])=Str([Due Date]),1,0)
PastDue:IIf([Today]>[Due Date],1,0)

What is the correct expression for my Access calculated columns?

Rey Obrero (Capricorn1)

try

CurrentMonth: IIf(Date()=[Due Date],1,0)
PastDue:IIf(Date() >[Due Date],1,0)

shieldsco

ASKER

Error Message :The expression cannot be used in a calculated table column

Rey Obrero (Capricorn1)

are your table in access db?

shieldsco

ASKER

yes it's table expression

shieldsco

ASKER

yes Access 2013 table

Rey Obrero (Capricorn1)

you have to do that in a query

shieldsco

ASKER

Why not in a table as a calculated column

Rey Obrero (Capricorn1)

so you want to use data macro?
upload a copy of your db

shieldsco

ASKER

Find attached
Policy-SharePoint---Copy.accdb

Rey Obrero (Capricorn1)

in the expression to calculate the values for Calculated field, you can only use the fields on the same table and hard coded values,
values such as Date() are not permitted.

another way to achieve the goal is to use DataMacro, see this link for reference
https://msdn.microsoft.com/en-us/library/office/ff973807(v=office.14).aspx

shieldsco

ASKER

Since I have no experience with Data Macros how would the following be created

CurrentMonth: IIf(Str([Today])=Str([Due Date]),1,0)
PastDue:IIf([Today]>[Due Date],1,0)

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial