DateDiff function data returning #Error

[Ajayi32]

I am using DateDiff function to calculate the amount of time between two datetime fields. Every record returns #Error. Attempted to sort data and I get Invalid Call Procedure.

I have tried to run the query on 3 different PC all with the same results.

The function is coded like this:
DateDiff("nn",[FIELD1],[FIELD2])

I have searched here and other places and have not been able to find a solution. Thanks in advance for your insight.

/uh

 

[boblarson]

are your fields named Field1 and Field2? Is that the exact code you are using (not replaced by some sample names)? If not, please post the real one you are using.

 

[Ajayi32]

The original field names were STARTDATE and STOPDATE. I thought that may be a problem and I changed it to STRTDATE and STPDATE.

I also thought maybe there was something quirky with the data so I made a new table and made the field names DOG and CAT if the DATE in the name caused the issue.

Everything I tried gave me the same result.

 

[boblarson]

Go to the VBA window and check to make sure that your VBA project is not named DateDiff.

If so, right click on it where I have the red box and change the project name.

 

[Ajayi32]

No, it isn't named that. The name currently is DB2.

 

[Ajayi32]

Thank you agin for your input. I am leaving work for today but will log in later tonight if you or anyelse comes up with anything. Or even another work around to calculate the difference in the two fields.

Thanks,

/uh

 

[dpw204]

I am using DateDiff function to calculate the amount of time between two datetime fields. Every record returns #Error. Attempted to sort data and I get Invalid Call Procedure.

I have tried to run the query on 3 different PC all with the same results.

The function is coded like this:
DateDiff("nn",[FIELD1],[FIELD2])

I have searched here and other places and have not been able to find a solution. Thanks in advance for your insight.

/uh

A debugging question: Is this code within a query? Or is it in VBA Code.

If the latter, a fundamental question: have you tried debugging, - stepping through the code and checking the values of [FIELD1] & [FIELD2]?

I'm wondering whether for some reason their may be a problem with the data source.

 

[spikepl]

Where is your code ?

"Invalid Call Procedure" indicates that something is rotten with the call. Post the complete SQL.

 

[Ajayi32]

I was within the query. But after banging around it for a good portion of the day it appears the issues was with the format portion. I had it like this:
DateDiff("nn",[FIELD1],[FIELD2])
which did not work.
I changed it to this:
DateDiff("n",[FIELD1],[FIELD2])
and it did work.
Kind of aggravating because nn is a format to use when look at the format properties in the help section.

Thanks again for all who viewed my question and all who took the time to respond.