Solved

Error for DateAdd formula on blank date field

Posted on 2014-11-22
5
199 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
5 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 100 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 40460041
try

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

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 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:SimonAdept
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now