Add a function to a filde

[One-eyed Phil]

Morning All
I am creating a database from Excel worksheets that were written previously. One of the worksheets had a list of tasks, the next cell listed the dates these tasks were carried out (if any). The next cell calculated the months since the last task. (=ISBLANK(C17), " ", ((Today()-C17)/30)).



This works well in the SS. I want to put the equivalent function into the table so that the field next to the one listing the date carried out will show me the same details. Is this possible? everytime I try to write using the expression builder I get an invalid syntax message. I assume the Excel 'formula will not work and tried to use various function, (IsBLANK, ISNULL) etc but cannot get it right,

Please help me to write the correct formula.

Thank You

Phil
(Access 2010)

[Micron]

Don't think of Access tables as spreadsheets or you will only struggle the whole time. You don't put functions in table fields, mainly because there's very few you could employ if you wanted to and probably should not anyway. For your issue, you should either
- post a sample of the data and specify if it is coming from Excel or is in Access
- clearly show what the desired result should look like and where it is supposed to be.

You manipulate data and show calculations in Access forms. You don't monkey with the data in tables, and storing calculations in tables is not a good idea 99% of the time.

[One-eyed Phil]

Thank you
It seems that using a table field is not correct procedure.
Therefore you suggest using forms, I'll need to work out how to do that so the calculation can be made and be available.
Can you suggest a funcyion that would be best to use. Not the exact formula but a basis to start with so I can learn as I go

phil

[One-eyed Phil]

In one table I have tasks listed as a field the next field is the date the task was performed.
What I want to do is find out how long ago the task was performed using the current date. (Or if it is booked in advance , how long until it is performed). For example, task A was performed 2.5 months ago, task B is due to be performed in 0.75 months

Therefore the days are calculated by subtracting the previous date, (performed) from the current date and divided by 30, to get the months.
Phil

[Micron]

Again, you need to show sample data and the expected outcomes. Or, I can guess and say that your table needs these fields (the date fields are really not optional IMO):
TaskId | TaskName | TaskDescription | DueDate | LastPerformed | Frequency

With that, you can know the task details plus the last time performed and the next time it is due based on the span between last performed and frequency. If it was last performed on 11/25/2020 and the frequency is 30 days, then it is not due yet. The due date can also be calculated and represented as appx. 12/30/2020 from those fields. That design can also tell you if it is overdue, or if not, how long before it is due.

[One-eyed Phil]

Problem solved.
I used the following formula in the form
=((Date() - [Date Performed])/30)