How to replace the "#Error"?

[Jakboi]

Hello,

I have a "Estimated Closing" area on my form. It takes the closing months and adds it to the now() function.

Now when you select time frame, it works fine. However I also have "Closed Sale" and "Lost Sale" in that list as well and when chosen i get this "#Error" because its text.

How can I replace the #Error with a N/A for not applicable.

I tried:

=DateAdd("m",[Time Frame],Now()) & IsError("NA")

Better yet:

If "Closed Sale" is chosen is there a way to put that in the "Estimated Closing" area along with "Lost Sale" if that is chosen as well.

Dont know the complexity to my question.

Thanks.

 

[KeithG]

You could use an IIF statement to check if it is a data value or a string. Something like below.

=IIf(isdate([TimeFrame])=True,DateAdd("m",[Time Frame],Now()),"N/A")

 

[Jakboi]

Thanks.

I use this on Excel but seems to be different.

=IF(ISERROR((L4-L6)/L6),(L4*100%),((L4-L6)/L6))


Now I tried this instead:

=IIf(isNumeric([TimeFrame])=True,DateAdd("m",[Time Frame],Now()),"N/A")

Because the Time Frame is just numbers 1-60 in a dropdown box. It also has the "Closed Sale", and "Lost Sale" in the dropdown box as well.

So isnt that saying if the Time Frame is numeric then added it to now date, if not then display "N/A".

Would something like this work:

=IIF(isError(DateAdd("m",[Time Frame],Now()),"N/A")

Trying something like I used for Excel, but not sure if that would work If I got it right.

 

[paindivine]

I almost think you are using the field wrong. Set the closing date in the field and leave it. Then create a different field for "Sales Status" or whatever... In that have "Pending Sale" "Closed Sale" "Lost Sale" etc... Then you wouldn't have to worry about it at all...

 

[Jakboi]

I almost think you are using the field wrong. Set the closing date in the field and leave it. Then create a different field for "Sales Status" or whatever... In that have "Pending Sale" "Closed Sale" "Lost Sale" etc... Then you wouldn't have to worry about it at all...

I had thought about that due to the fact of how I was doing it, regarding numbers and text in a dropdown and using functions counting that. I will do that. Thanks for the suggestion.