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?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don" <(E-Mail Removed)> wrote in message
news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
> 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?

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" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don" <(E-Mail Removed)> wrote in message
> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
> > 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?
>
>

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don" <(E-Mail Removed)> wrote in message
news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
>> > 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?

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

"Allen Browne" wrote:

> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don" <(E-Mail Removed)> wrote in message
> news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
> >> > 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?
>
>

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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don" <(E-Mail Removed)> wrote in message
news:3A154A1F-EE03-4E0E-ABC7-(E-Mail Removed)...
> Code compiles. I created an error before trying the compiler and it gave
> an
> error response. I appreaciate your help.
>
> "Allen Browne" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
>> >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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
>> >> > 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?

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" wrote:

> 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?
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don" <(E-Mail Removed)> wrote in message
> news:3A154A1F-EE03-4E0E-ABC7-(E-Mail Removed)...
> > Code compiles. I created an error before trying the compiler and it gave
> > an
> > error response. I appreaciate your help.
> >
> > "Allen Browne" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
> >> >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" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
> >> >> > 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?
>
>

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don" <(E-Mail Removed)> wrote in message
news:998D2932-F25B-493D-A11C-(E-Mail Removed)...
> 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" wrote:
>
>> 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?
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Don" <(E-Mail Removed)> wrote in message
>> news:3A154A1F-EE03-4E0E-ABC7-(E-Mail Removed)...
>> > Code compiles. I created an error before trying the compiler and it
>> > gave
>> > an
>> > error response. I appreaciate your help.
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
>> >> >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" wrote:
>> >> >
>> >> >> 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" <(E-Mail Removed)> wrote in message
>> >> >> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
>> >> >> > 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?

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" wrote:

> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don" <(E-Mail Removed)> wrote in message
> news:998D2932-F25B-493D-A11C-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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?
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Don" <(E-Mail Removed)> wrote in message
> >> news:3A154A1F-EE03-4E0E-ABC7-(E-Mail Removed)...
> >> > Code compiles. I created an error before trying the compiler and it
> >> > gave
> >> > an
> >> > error response. I appreaciate your help.
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> news:3F4DE520-E9FC-43C9-8F92-(E-Mail Removed)...
> >> >> >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" wrote:
> >> >> >
> >> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> >> news:2CE80518-FC50-4E2F-8D7B-(E-Mail Removed)...
> >> >> >> > 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?
>
>

That's great news, Don.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don" <(E-Mail Removed)> wrote in message
news:F64D2B24-C9E4-44F7-8A97-(E-Mail Removed)...
>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.