Getting the difference between dates

[trident]

I have created a form wherein I set up the following dates:

Text119 (current date) formula used is =Date()
Submit date box - set up as date entered by the user
Deadline date - set us as date entered by the user

If the submit date box is empty, the difference will be [deadline date]-[text119]
if the submit date box is not empty, the difference will be 0, the formula set up for the difference is as shown below:

=IIf(IsNull([submit]),[DeadlineDate]-[text119],0)

the result showing is an error #NAME?

however, earlier it was showing the correct difference.

can anybody assist me on this.

[orange]

Don't overlook Google for researching

See Datediff

[Missinglinq]

I agree with orange vis-à-vis using DateDiff() for this kind of thing, but that's not causing your problem, here. When #NAME is returned in a Control, especially in a Calculated Control, like this, it means that Access cannot find one or more Controls that are part of the Calculation. You could have a Control's name misspelled,or it's simply not available to the Form. So, first off, check the spelling of all Control names.

It's odd that you have descriptive names for submit and DeadlineDate, but the Access-assigned name of text119 for the third Control. Have you changed one of the Control names since the Form last worked correctly? If so, double-check that these changes 'took.' In later editions of Access, sometimes if you change a property, such as Name, and exit the Property Pane or save the file without first moving to another property, the change doesn't actually occur.

Is it possible that when submit is empty, either DeadlineDate or text119 could also be empty?

Linq ;0)>

[trident]

Dear Missinglinq and Orange,

thank you for taking time.

I have made a duplicate of the form before the additional changes I made, I went back to the old form and compared the property sheet of both forms. The property sheet, table data are all the same.

I tried creating the same formula, copy the formula from the duplicate form and the same error occurred. #name?

I really am stump on this. Hope additional explanation can be given.

Trident.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

...Hope additional explanation can be given...Trident.

For us to help anymore, I think you need to follow June7's suggestion and post the file.

Linq ;0)>