Date is not a valid expression

[Don]

I've recently started getting two error messages regarding the use of "Date"
in MS Access. One is "Unknown Function 'Date' in Validation Expression...".
The other is "The Microsoft Jet Database Engine does not recognize 'Date' as
a valid field name or expression'. It's only happeneing in one of the two
databases I have. What's happened? How can I correct this?

 

[Allen Browne]

First thing to check would be whether there is a problem with library
references on the machines that exhibit the problem.

Open any code window, and choose References on the Tools menu. Any checked
references marked "MISSING"?

More info:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html

 

[Don]

I have looked in the references. I do not see "MISSING" after any checked
item in the ActiveX Data Objects 2.1 Library.

 

[Allen Browne]

What libraries are checked? You must have Access and VBA, and it sounds like
you have ADO as well.

If the word "MISSING" does not appear beside them, does the code compile?
(Compile on Debug menu, in the code window.)

 

[Don]

Code compiles. I created an error before trying the compiler and it gave an
error response. I appreaciate your help.

 

[Allen Browne]

In what context are you getting these errors?

In a query? Post the SQL statement (SQL View, on View menu.)

In VBA code? Post the line that generates the error, and indicate the error
number.

What version of Access?

What operating system?

 

[Don]

OS - Windows XP Home Edition Version 2002 Service Pack 2.
Access 2000.
I am having no problem in my other database, and this one was working
properly before I had a virus contamination. The contamination was cleaned
up.

The problem occurs differently on a form as opposed to a report.

On a form - after typing the 1st character in any field, the message
"Unknown function 'Date' in validation expression or default value in
'tblPlayers.Birthdate'. This is happening on the main form, but doesn't
happen on the form's sub-form even when there is no entry to the main form.

On a report - after clicking on a menu to open the report, in one case the
form wanting a Paramenter Value for Date appears. The report will open. In
the other case the message is "The MS Jet Database does not recognize 'Date'
as a valid field name or expression'. This message was also appearing
concerning 'Format'before I removed the entire entry.

Again, none of this occurs in my other data base.

 

[Allen Browne]

You may be able to work around the immediate issue if you open tblPlayers in
design view, and remove any expression referring to Date from its properties
(e.g. the field's Default Value.) Similarly, remove the expression from any
properties of the text box on the form, and possibly code of the form or
controls.

But if you had the same problem with Format() as well, then it *is* a
problem with references. Jot down the names of all the references you are
using, as well as the full path to the file. It's hard to read this as the
window is generally to small to display it, but you can open the Immediate
Window (Ctrl+G) and enter:
? References("ADO").FullPath
to see the full path to each reference.

Then:
1. Uncheck all references except Access and VBA (which you can't uncheck.)

2. Close the database.

3. Unregister each library, and re-register it. Details in the article
already linked:
http://allenbrowne.com/ser-38.html

4. Open the database again, and select those same references again, or at
least the ones you really must have to get the code to compile.

If it's still broken at this stage, there is another issue here. It could be
a damaged library (particularly after a viral infection), or it could be a
corrupt database (the kind of thing a decompile might fix.)

 

[Don]

I couldn't get the "Missing" message from Access 2000 in the Immediate
Window. I took the data base to my son who is using Access 2003. The label
came up immediately. After unchecking the involved file, the problem was
solved.

Thanks for your help.

 

[Allen Browne]

That's great news, Don.

(There do seem to be cases where A2000 doesn't identify a missing
reference.)