Jump to content

Adding days to current Date not working

This topic is 2917 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi all


I hope someone can stop me from tearing my hair out.

I am building a database for an optometry practice. I am trying to build a recall  system within the database.


I have a field Recall_Name, Recall_Due and Recall_Date.


The idea is the user selects the recall name(e.g. Regular review) and sets the time in days, weeks or months.  the date the recall is due then shows in Recall_Date field.

Recall_Name is a text field

 Recall_Due is a text field

Recall_Date.is a calculation field. (See attached screen shot)


It all works very well except for 2 time scales.  Both 1 month and 1 year recall show up blank in the Recall_Date field.


This is driving me nuts. I can't understand what I am doing differently in these 2 time scales.


Any help greatly appreciated'


Thanks in advance.






Link to comment
Share on other sites

I don't think that's a very good method, but at least it should work as planned. if your Case() function doesn't produce a result, then you have a case that isn't covered by any of your tests. That's easy to verify by including a default result.


Regarding "1 year", I don't see that you have included that eventuality in your calculation, so obviously no result there.


Regarding "1 month", the most likely reason is that the data in the Recall_Due field is not what you think it is. Iit could be something as trivial as having a space trailing the value in the value list definition. Or perhaps you have "1 months" instead of "1 month"?

Link to comment
Share on other sites

  • Newbies

Thanks comment

I have tried copying from the value list and pasting into the calculation, but still not working for 1 month or 12 months.

You indicated there is a better method to use. I would love to hear any suggestions you might have.

Thanks again


Link to comment
Share on other sites

I would tend to use two separate fields to select a number and a unit of time. But even with your current method of selection, you could reduce the calculation to =

Let ( [
n = LeftWords ( Recall_Due ; 1 ) ;
unit = Left ( RightWords ( Recall_Due ; 1 ) ; 1 ) ;
d = Day ( Get ( CurrentDate ) ) + n * ( unit = "d" ) + 7 * n * ( unit = "w" ) ;
m = Month ( Get ( CurrentDate ) ) + n * ( unit = "m" ) ;
y = Year ( Get ( CurrentDate ) ) + n * ( unit = "y" )
] ;
Date ( m ; d ; y )

which is not only shorter and more accurate - but also allows you to add any combination to your value list, without having to modify the formula.

  • Like 1
Link to comment
Share on other sites

This topic is 2917 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.