DateDiff() error (1 Viewer)

[usm01]

i am using DateDiff to display difference between to dates , but it does not work.

1)-using vba code (after/before update ) event does not work.
2)-using unbound control source gives me error "the expression you entered has a function containing wrong number of arguments"
i am using this control source
=IIf (IsNull([startdate]) - "" - DateDiff("m" - [enddate] - [startdate]) + 1)


and i even used this
=DateDiff("m" - [enddate] - [startdate]) + 1
but gives me same error.

what am i doing wrong.

the file is attached.
Plz help.

 

[Rich]

Try =DateDiff("m" , [enddate] , [startdate]) + 1

 

[usm01]

same error.

 

[missinglinq]

The first thing you're doing wrong in the example you posted is trying to do the calculations in the Before Update and AfterUpdate events of the field you're trying to populate! The calculations, if done at the form level, have to be done in the fields where the components of the calculations are being entered, i.e. StartDate and EndDate.

The next thing you're doing wrong is placing StartDate and EndDate in the wrong order. Used as you have it, with the EndDate first, will result in a negative number.

The final thing you're doing wrong is attempting to store the months difference in the underlying table. This type of calculated value should never be saved to the table, but simply re-calculated whenever needed, such as in a report.

Delete the code you currently have and use this, which will place the calculated value in your unbound textbox, Text9.

Private Sub startdate_AfterUpdate()
 If Not IsNull(Me.startdate) And Not IsNull(Me.enddate) Then
   Me.Text9 = DateDiff("m", Me.startdate, Me.enddate) + 1
 End If
End Sub

Private Sub enddate_AfterUpdate()
 If Not IsNull(Me.startdate) And Not IsNull(Me.enddate) Then
   Me.Text9 = DateDiff("m", Me.startdate, Me.enddate) + 1
 End If
End Sub

Private Sub Form_Current()
 If Not IsNull(Me.startdate) And Not IsNull(Me.enddate) Then
   Me.Text9 = DateDiff("m", Me.startdate, Me.enddate) + 1
 End If
End Sub

I don't understand the + 1 on the end, which adds a month to the actual difference, in months, but I've added it like you had it.

FYI, the AfterUpdate and BeforeUpdate events of a textbox only fire if a value has been physically entered into it. They do not fire if the have been populated thru code.

Good luck with your project.

 

[usm01]

Thanks For Helping.
i did these blunders when i could not make the code work.

The Solution i found was that there something wrong with my system file or the version of access 2003 i am using.
i tried the same code on different Pc with another version of access 2003 and it worked.
Now i am using that file and it works.

For (+1) at the end of code, i read somewhere for inclusive of start and end date but as i am calculating month so you are right there is no need of using it.

Thanks missinglinq.

 

[missinglinq]

Glad you got it working!