I am trying to calculate the datediff between a contract start date and contract end date. I entered the following criteria as a calulated field on my form and this works fine.

=DateDiff("m",[Contract Start Date][Contract End Date])

I entered this criteria to find the datediff between the current date and the contract end date but I get the #name? error.

=DateDiff("m",[Now()],[Contract End Date])

When a contracts running time is < = 3 months I want to display a msgbox when a supplier that meets this criteria is selected from my combobox. I entered the following code but Im not too good with vb so dont know if this will do the trick.

Private Sub Combo80_Click()
If Running_Time <= 3 Then
MsgBox "The contract is due to expire in ? months"
End If
End Sub

can anyone help?
Thanks

Both Now and Date are reserved words in Access not field names, you don't enclose them with brackets.
=DateDiff("m",Date(),[EndOfContract])
you'll have to watch for negative values if the contract has already expired.
HTH

Thanks Rich that worked perfectly and you were right about the negative values when a contract has not expired. How do I prevent this?

The vb code I have above is working fine when I select a supplier it will indicate it is due to expire with a msgbox, however I have a number of contracts in my subform for every supplier how can I highlight which contract it is? and can I make the computer beep while the msgbox is displayed?

Any feedback welcome!