DateAdd Error

[AFKillbait]

I created a form to display a number of training dates based on a query that combines two of my tables. In the query I used an expression to automatically enter "N/A" into any fields that didn't have dates in them. After I created the form I created several text boxes with the DateAdd expression:

=DateAdd("m",12,[Field Name])

to display the due dates for the associated training. So long as there is a date in the query date box it displays the due date just fine, but if the box is displaying "N/A" I get "#Error" displayed. Is there a way to get the Due Date box to also display "N/A" like the original date box?

The other problem I am having is that the query date box only displays the dates in Short Date format, and I would like them displayed in Medium Date, is it possible to do that with the following expression?

nDate:Nz(YourDateField,"N/A")

 

[ajetrumpet]

one way to get around this would be to use visual basic instead of the CS. in basic, u can use the iserror() function which i think will cover this. in the CS, i don't believe that function will work. in basic, u would use the oncurrent() event, which is basically the same as using the CS of a control

 

[missinglinq]

I agree with Adam that this is best done using VBA code in the form's module. Obviously, you can't use DateAdd() when the field contains "N/A" instead of a valid date!

I'd scrap everything you've got to date and and use this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.YourDateField) Then
  Me.YourDateField = "N/A"
  Me.DueDateField = "N/A"
 Else
  Me.DueDateField = DateAdd("m", 12, Me.YourDateField)
 End If
End Sub

where YourDateField is the original date and DueDateField is the field that holds the renewal date.

Since you want both of these fields to be able to hold "N/A" these fields must be defined as Text, not as Date/Time datatypes. As long as the text in the YourDateField control looks like a valid date, DateAdd() will function correctly.

In running reports on this data you'll have to remember that some records will have "N/A" in these fields rather than "dates" and plan accordingly.

 

[AFKillbait]

I tried using the code you provided, however now it does not display N/A in the original date field, and the Due Date field is blank altogether, even for the boxes that have dates. Is there something else I need to include to get it to work correctly?

 

[AFKillbait]

Ok, figured out what I was doing wrong. Was me forgetting to change the table field format to text. Thanks for your help

 

[missinglinq]

Glad we could help!