DateDiff

[Guest]

I have an Access DB that has a field named StartDate and a field named
EndDate. I need to calculate the difference between the two dates. I'm
using the DateDiff function but all I get back is "?Name". I'm getting so
irritated because I know that is something so small or stupid and I'm just
overlooking it. I'm normally very good at databases but I just can't get
this. Can anyone help??

Here is the function that I have right now:

=DateDiff("d",StartDate,EndDate)

Does it matter how the other fields are set up or what? I'm so ready to
scream!

 

[Guest]

In what context are you trying to do this? Is it on a form, in a query...
The problem is often one of context of scope where you might need to fully
qualify where the fields are.

 

[Guest]

I was trying to do it in a form. I also tried doing it in a table but that
gave me a whole new scope of errors. Do you have any suggestions on what I'm
doing wrong or how to fix it? Any examples or templates I can look at?

I'm working on so many other projects and this one is just taking up more
time than it should but it is imperitive that I get it to work properly very
soon.

Thank you,

Jamie Dale

 

[fredg]

I have an Access DB that has a field named StartDate and a field named
EndDate. I need to calculate the difference between the two dates. I'm
using the DateDiff function but all I get back is "?Name". I'm getting so
irritated because I know that is something so small or stupid and I'm just
overlooking it. I'm normally very good at databases but I just can't get
this. Can anyone help??

Here is the function that I have right now:

=DateDiff("d",StartDate,EndDate)

Does it matter how the other fields are set up or what? I'm so ready to
scream!

Assuming startdate and enddate are correctly spelled and are date
datatype fields ....

Make sure the Name of the control is not startdate nor enddate.

 

[Guest]

Yes I triple checked my spelling. That was my first thought. And the
control is not named startdate or enddate. And they do have the date/time
datatype. Any other suggestions?

Jamie

 

[fredg]

Yes I triple checked my spelling. That was my first thought. And the
control is not named startdate or enddate. And they do have the date/time
datatype. Any other suggestions?

Jamie

Are [StartDate] and [Enddate] fields in the record source of the form
this is being done in?
What do you get if you write:
=[StartDate]
or
= [EndDate] ?

If both return a date value, then I would suspect your problem is with
the DateDiff() function.

Click Ctrl + G (to open the debug window).
Type
? DateDiff("d",#10/7/2004#,Date())
Press Enter.
Depending upon when you do this, the answer should be 7 (or 8 if on
the 15th)
If you get an error message, then you probably have a missing
reference.


While in the debug window:
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Griz]

Yes I triple checked my spelling. That was my first thought. And the
control is not named startdate or enddate. And they do have the date/time
datatype. Any other suggestions?

Jamie

Your formula works for me regardless of the control name, or whether the
datatype is set to text or date/time. the only time I received the same
error was when I spelled EndDate wrong (Awfully easy to miss that second d,
at least for me )

A suggestion if nothing else works. Some times a form seems to become
"corrupted" somehow. Things that should work don't and things that used to
work stop working. In design view select everything and copy it. Create a
new form and paste everything into it. Save the new form, replacing the
old one. You will probably have to reset form properties. You should
probably also save any code behind the form somewhere too. ATM I can't
remember if you lose it or not. May seem drastic but when all else failed
it has fixed similar problems for me in the past.

Griz

 

[Guest]

Thank you so much for your help. However I'm still getting the same message.
I even just made up a BS DB just to see if it was something in my other one
and I'm getting the same error message. I appreciate everyones help. Thank
you. I just don't know what I'm doing wrong.

Jamie

 

[Arvin Meyer]

Thank you so much for your help. However I'm still getting the same message.
I even just made up a BS DB just to see if it was something in my other one
and I'm getting the same error message. I appreciate everyones help. Thank
you. I just don't know what I'm doing wrong.

Check for the "MISSING REFERENCE" as outlined by Fred. When VBA loses a
reference, no matter what its referencing, you will get a Naming error
because any reference causes a misplacement of the libraries. The Date and
String functions are especially vulnerable.

Just open a code window and click on Tools ... References and you'll see it
right away.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access