Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error for DateAdd formula on blank date field

Posted on 2014-11-22
5
Medium Priority
?
217 Views
Last Modified: 2014-11-22
=IIf(IsError(DateAdd("d",30,[Text44])),"",DateAdd("d",30,[Text44]))

When the above formula is evaluated for a Text44 textbox  with a "" value, I get an error as the output for the second text box.

What can I do make the formula output a blank value?

Thanks

Glen
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Assisted Solution

by:Simon
Simon earned 400 total points
ID: 40460035
The problem is that IIF evaluates both parts, even though it only returns a value for one of them.

This is a problem when using calculated controls. I tend to use event handlers that fire after controls are modified to set dependent fields. In this case I'd check that the value in Text44 is a date and only then calculate and populate the dependent field.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 40460041
try

= IIf([Text44] & "" = "", "", DateAdd("d", 30, [Text44]))
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1200 total points
ID: 40460049
Or you could create a function:
Public Function DateAddPlus(Interval as string, Incr as Double, SomeDate as Variant) as Variant

    IF Len(Trim(SomeDate & " ")) = 0 Then
        DateAddPlus = ""
    Else
        DateAddPlus = DateAdd(Interval, Incr, SomeDate)
    End If

End Function

Open in new window

The advantage of using a function is that the function will be evaluated when you go to a new record and when you make a change in the the first date control.  The syntax you would use to call this function within your Control Source would be something like:

=DateAddPlus("d",30,[Forms]![YourFormName].[text44])
0
 

Author Closing Comment

by:GPSPOW
ID: 40460056
Thanks to all who gave me the good recommendations.

Glen
0
 
LVL 18

Expert Comment

by:Simon
ID: 40460057
I just knocked up a quick test. If the input date field is blank, a simple =DateAdd() calculated field stays blank until a valid date is entered into the input date field. My test used a bound date field to as the input date field. If using unbound fields, using an input mask or validation rule would prevent any non-date values being entered.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question