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
Medium Priority
217 Views

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

LVL 18

Assisted Solution

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

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

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

LVL 48

Accepted Solution

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
Else
End If

End Function
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:

0

Author Closing Comment

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

Glen
0

LVL 18

Expert Comment

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

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…
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll