Help me calculate remaining days in a table.

[mraccess101]

I'm trying to step from Excel to Access and I have some issues to translate functions that I used in Excel to Access.

This is how it looks in Excel.

And I have issues to translate the following function in Excel to Access.

=IF(AND(I6=0),"Done",K6-A$4)

A$4 is related to cell where it states the function =now()


Basically what I want is to calculate in the tablecolumn is the following.



In the column "Days remaining" I want the remaining days from "date to pay" and today. If the amount states "0" then the outcome must be "done".

Im not a native English speaker. I hope I explaned enough, if not, please tell me and I will try in other way to explane.

 

[Beetle]

First, you would not do this in the table, you would do it using a calculated field in a query. The following example uses the IIf and Date() functions to calculate the value;

 

[mraccess101]

Thanks a lot. But is it possible to do the right on the table?

 

[John Big Booty]

Thanks a lot. But is it possible to do the right on the table?

Access will not do calculations at Table level, if you want to do things that way then perhaps you need to look at transferring your project to Excel.

The tables in Access are only for storing data (your user should have no direct access to those tables), and in fact calculated values should (in general) not be stored in the tables. Calculations are done either at Form/Report level or in the queries that are feeding those Forms/Reports.

An important concept that you will need to grasp when designing an efficient DB is that of Data Normalisation, you may also want to work through a tutorial on the subject.