Access2000 and Day calculations

[Alynn Baker]

I have had to switch from Access97 to Access2000 for a variety of reasons. I
converted my database and most things are working. However, I am getting an
error when using a query I have used for 7 years in version 97. It
calculates how many days late a payment is.

One of the fields in my database is called date and it signifies what date
the bill is sent.

In my query, I used a formula that worked in Access 97 as follows:
late: Date()-[date]

This told me how many days later today was from the day the bill was sent
out. Now (in 2000) I get the error: Undefined function 'Date' in expression.
Pressing "help" gives me "Undefined function <name> in expression. (Error
3085) You entered an SQL expression that includes a Function procedure name
that cannot be recognized. Make sure the function exists, that it can be
used in SQL expressions, or check the expression to make sure you entered
the name correctly"

I would appreciate some help with this. I changed the name of my field
"date" to something else but it still happened. But it was fine in Access97.
Is there a workaround or another way to calculate days elapsed between an
earlier date and now?

Thanks.

 

[fredg]

I have had to switch from Access97 to Access2000 for a variety of reasons. I
converted my database and most things are working. However, I am getting an
error when using a query I have used for 7 years in version 97. It
calculates how many days late a payment is.

One of the fields in my database is called date and it signifies what date
the bill is sent.

In my query, I used a formula that worked in Access 97 as follows:
late: Date()-[date]

This told me how many days later today was from the day the bill was sent
out. Now (in 2000) I get the error: Undefined function 'Date' in expression.
Pressing "help" gives me "Undefined function <name> in expression. (Error
3085) You entered an SQL expression that includes a Function procedure name
that cannot be recognized. Make sure the function exists, that it can be
used in SQL expressions, or check the expression to make sure you entered
the name correctly"

I would appreciate some help with this. I changed the name of my field
"date" to something else but it still happened. But it was fine in Access97.
Is there a workaround or another way to calculate days elapsed between an
earlier date and now?

Thanks.

Two comments regarding your post.
1) Your PC has a missing reference.
Open any module in Design view (or click Ctrl + G).
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

2) Your use of the word "Date" as a field name...
You have been quite fortunate that your code worked without error for
this long.
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Alynn Baker]

Thank you! Your first suggestion worked perfectly.

 

[Alynn Baker]

Thank you! Your first suggestion worked perfectly.

I'll see if I can change the word "date" if my program fails again. Is there
a shortcut to change all references in all my millions of queries and
reports?