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.

 

jeff

 

 

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"?

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

Jeff

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.

Gee I see why I am a novice. Thanks very much. I'll give it a try.

Sweet!