Access 2013 Calculated Date Columns in Table

shieldsco
shieldsco used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try

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

Author

Commented:
Error Message :The expression cannot be used in a calculated table column
Top Expert 2016

Commented:
are your table in access db?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
yes it's table expression

Author

Commented:
yes Access 2013 table
Top Expert 2016

Commented:
you have to do that in a query

Author

Commented:
Why not in a table as a calculated column
Top Expert 2016

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

Author

Commented:
Top Expert 2016

Commented:
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

Author

Commented:
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)
Top Expert 2016
Commented:
like what i said, you can only refer to Fields in the same table.
a work around is to create a new Date/time field , call it CurrDate
- to populate the field CurrDate with the current date, you need to run an update query

Update tableName set [CurrDate]= Date()

now you can use this expressions

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial