DATE()

  • Thread starter Thread starter William Gargan
  • Start date Start date
W

William Gargan

Hi all,

I recently had an existing query formula fail. I was a simple formula
where an existing date field was subtracted from Date() to get elapsed
days.

ELAPSED: DATE()-BLAH_DATE

It only failed in Access 2000 versions that were updated to SR-3. It
worked fine in SR-2 and prior.

I was able to replace Date() with Now() and rounded to get whole days.

ELAPSED: ROUND(NOW()-BLAH_DATE,1)

Any knowledge why Microsoft voided the Date() in SR-3?

Any better solution than my simple one?

Thanks,
Bill Gargan
 
The correct way of finding the time elapsed between two
dates is to use DateDiff e.g.

DateDiff("d" ,Date() ,Blah_Date)

Hope This Helps
Gerald Stanley MCSD
 
Hi all,

I recently had an existing query formula fail. I was a simple formula
where an existing date field was subtracted from Date() to get elapsed
days.

ELAPSED: DATE()-BLAH_DATE

It only failed in Access 2000 versions that were updated to SR-3. It
worked fine in SR-2 and prior.

I was able to replace Date() with Now() and rounded to get whole days.

ELAPSED: ROUND(NOW()-BLAH_DATE,1)

Any knowledge why Microsoft voided the Date() in SR-3?

Any better solution than my simple one?

Thanks,
Bill Gargan
Click to expand...

Bill,
The machine may have a missing reference.
Open any module in Design view.
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.
 
This sounds like it might be a references problem.

When this happens, you need to take steps to let Access repair the broken
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are Doug Steele's instructions for how to do it:

****Quote****
Open any code module (or open the Immediate Window, using Ctrl-G, provided
you haven't selected the "keep debug window on top" option). Select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in).

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.
****Unquote****

So, follow those instructions and see if your problem goes away.
 
Hi Fred,

Your reply concurred with another -- while one suggested a different
formula that still uses the Date() function for getting today's date.

My problem with your reply is that this is a fairly minor database
that has no modules. Just tables, forms, queries and reports -- no
modules.

If I open a query, I do not get "references" in the tools menu. I
opened a module in Northwind and can see that the menu item does exist
for modules. It does not for queries.

The thing that bothers me the most is that date() works fine in PCs
with Access 2000 that have not been updated to SP-3.

Thanks,
Bill Gargan
 
Hi Fred,

Your reply concurred with another -- while one suggested a different
formula that still uses the Date() function for getting today's date.

My problem with your reply is that this is a fairly minor database
that has no modules. Just tables, forms, queries and reports -- no
modules.

If I open a query, I do not get "references" in the tools menu. I
opened a module in Northwind and can see that the menu item does exist
for modules. It does not for queries.

The thing that bothers me the most is that date() works fine in PCs
with Access 2000 that have not been updated to SP-3.

Thanks,
Bill Gargan
Click to expand...

Bill
Date() is a function.
Functions are stored in libraries.
Your database can't find the (or a) library.
You need to open any code window to get to the reference list.

Open any module, (you don't need to have any code in it), or simply
press Ctrl + G and it will display the debug window.
Click on Tools + References
If any are marked MISSING, uncheck that reference.
If none are marked missing, refer to the previous post and read the
appropriate Access knowledge base article to determine if the correct
libraries are the ones that are checked.
By the way, John Spencer's post gives a bit more detail if none are
marked missing.

Also....
=DateDiff("d",[DateField],Date())
will return the same value in days as
= Date() - [DateField]
so you could use either expression to get the number of days between
the current date and a date in your DateField.

Hope this has helped.
 
Back
Top