Why does my new Access 2003 not recognize Date()?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barb - 20 May 2005 22:44 GMT

We migrated from Access 2000 to Access 2003 onto Windows XP and now my
database does not recognize the expression =Date(), either in tables or
queries. I NEED this - any help would be greatly appreciated. Is there
another way to say "today"? Do I need to do something to make Access 2003
recognize "Date()"?

We have been able to temporarily make things work with =Now() but this does
not give the functionality we need.
Thanks for any help.
-Barb

Reply to this Message

Jeff Conrad - 20 May 2005 23:38 GMT

> We migrated from Access 2000 to Access 2003 onto Windows XP and now my
> database does not recognize the expression =Date(), either in tables or
[quoted text clipped - 4 lines]
> We have been able to temporarily make things work with =Now() but this does
> not give the functionality we need.

Hi Barb,

It is a References problem. The arch-nemesis of Access is missing references.

To fix the References problem follow these steps:
- Open any module in Design view.
- On the Tools menu, click References.
- At least one Reference should say "Missing". WRITE down the one(s) missing.
- Click to clear the check box for the type library or object library marked as "Missing:."
- Close the References Window box.
- Open the References Window back up. Hopefully, nothing should say Missing this time.
- Now go find that library/project(s) in the list that was missing.
- If more than one were missing, find the others and check them as well.
- Close the References box again.
- Now re-compile again. Debug--Compile.
- Hopefully you should not see any more compile errors.

If you'd like to read more about References, here's more info than you could possibly ever want to
know:

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

http://allenbrowne.com.au/ser-38.html

http://www.trigeminal.com/usenet/usenet026.asp

http://www.papwalker.com/dllhell/index-page2.html

http://www.access.qbuilt.com/html/gem_tips1.html#DAOLibRef

References that you must set when you use Microsoft Office Access 2003:
http://support.microsoft.com/?id=825796

ACC2002: References That You Must Set When You Work with Microsoft Access:
http://support.microsoft.com/?id=283115

ACC2000: How Access 2000 Resolves Visual Basic for Applications References
http://support.microsoft.com/?id=248941

ACC2000: How to Resolve Reference Issues in an Access Database
http://support.microsoft.com/?id=310803

List of the references that you must set when you work with Access 2000 or Access 97 database:
http://support.microsoft.com/?id=197110

ACC97: References That You Must Set When You Work with Microsoft Access
http://support.microsoft.com/?id=175484

ACC: VBA Functions Break in Database with Missing References
http://support.microsoft.com/?id=160870

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Barb@CP - 26 May 2005 01:07 GMT

Jeff, Thanks so much for your response. Unfortunately, this after following
your suggestions, the problem remains. And, I discovered a new function that
is not being recognized (first).

If it helps to suggest a course of action, the message I get when I try to
use Date() as the default value in a field is, "Unknown function 'Date' in
validation expression or default value on (field name)."

Thanks again for your suggestions.
Barb

> > We migrated from Access 2000 to Access 2003 onto Windows XP and now my
> > database does not recognize the expression =Date(), either in tables or
[quoted text clipped - 55 lines]
> ACC: VBA Functions Break in Database with Missing References
> http://support.microsoft.com/?id=160870

Reply to this Message

Jeff Conrad - 26 May 2005 02:15 GMT

> Jeff, Thanks so much for your response. Unfortunately, this after following
> your suggestions, the problem remains. And, I discovered a new function that
[quoted text clipped - 5 lines]
>
> Thanks again for your suggestions.

Walk me through exactly what steps you tried Barb.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Barb@CP - 26 May 2005 18:33 GMT

Here is what I did: I went into modules (there are no existing modules listed
in our database so I clicked 'new' to open the VB editor) and opened
References.

-There was one ref. marked MISSING (Kodak Image Edit Controls). I un-checked
it & closed references. When I re-opened references it was not there to
re-check, but it re-appears after closing & opening the entire database. (I
have no idea what this is or what it's used for. For a short time we got an
error message when opening the database saying this reference was missing,
but no longer.)

-when I clicked the "compile database" menu item, nothing seemed to happen.
(I do not do any programming, not even SQL, and am not familliar with this
function.)

-I looked at the web site
(http://www.accessmvp.com/djsteele/AccessReferenceErrors.html) in your list
and followed the instructions to try un-checking other items in the
referneces box, then went back and re-checked them. That did not seem to have
any impact either.

Barb

> > Jeff, Thanks so much for your response. Unfortunately, this after following
> > your suggestions, the problem remains. And, I discovered a new function that
[quoted text clipped - 7 lines]
>
> Walk me through exactly what steps you tried Barb.

Reply to this Message

Jeff Conrad - 26 May 2005 18:52 GMT

> Here is what I did: I went into modules (there are no existing modules listed
> in our database so I clicked 'new' to open the VB editor) and opened
[quoted text clipped - 16 lines]
> referneces box, then went back and re-checked them. That did not seem to have
> any impact either.

You probably do not need the reference to the Kodak one, but we'll check
to be sure.

1. Go to the VBA Editor window as you did before and open the References
dialog box.

Before we go any further, please list for me all the References that have a check
mark in this box.

2. If Kodak is listed and says MISSING then uncheck it.

3. Go to Debug | Compile and see if your code compiles. If you receive *no*
error messages then your database does not need this reference.

4. Now test to see if the Date function works.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Barb@CP - 26 May 2005 22:17 GMT

Yikes! (from excited to bummed). After I unchecked the Kodak Image Edit
Controls the Date() seemed to work, and so did 'left.' But then, after I
closed and opened the datebase, I can no longer edit donations in our data
input form! (already checked field and form properties for 'allow edits,' &
'locked'). AND this time the Kodak Image Edit Controls did not re-appear in
the References list - it is no longer available for me to check....argh.

All the referneces that have a check mark are:

-Visual Basic for Applications
-Microsoft Access 11.0 Object Library
-OLE Automation
-Microsoft ActiveX Data Objects 2.1 library
-Microsoft Forms 2.0 Object Library

that's it.

Barb

> > Here is what I did: I went into modules (there are no existing modules listed
> > in our database so I clicked 'new' to open the VB editor) and opened
[quoted text clipped - 32 lines]
>
> 4. Now test to see if the Date function works.

Reply to this Message

Jeff Conrad - 27 May 2005 02:54 GMT

> Yikes! (from excited to bummed). After I unchecked the Kodak Image Edit
> Controls the Date() seemed to work, and so did 'left.' But then, after I
[quoted text clipped - 12 lines]
>
> that's it.

Hi Barb,

I really do not think you need the Kodak Image Control, but I
cannot be positive. If the code compiles just fine without it then
I think you should leave it off.

Without knowing more about what your data entry form is like
the only thing I can suggest right now is seeing if you need a
reference to the DAO object library. Find the DAO 3.6 library
in the References list and put a check next to it. Compile the
code and then tell me the results. Then we'll go from there.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Barb@CP - 27 May 2005 21:53 GMT

Jeff, You are so kind to help me. Unfortunately, checking the DAO library did
not allow me to edit my records. Compiling showed no errors. The donation
edit section of our main data entry form is a sub-form (to enter data in
related tables). We are a nonprofit and the database tracks contributions and
other data on donors. The main form edits the main table with name and
address, etc., while the related tables (edited through the sub-forms) are
donations, contact records, etc.) There are several sub-forms on the main
form with tabs to select each. None of these sub-forms are allowing record
edits, but the main form is. When creating a new record in a related table
through a sub-form , the donor ID (primary key) is automatically inserted in
the record. Does any of this information suggest next steps?

Thanks,
Barb

> > Yikes! (from excited to bummed). After I unchecked the Kodak Image Edit
> > Controls the Date() seemed to work, and so did 'left.' But then, after I
[quoted text clipped - 24 lines]
> in the References list and put a check next to it. Compile the
> code and then tell me the results. Then we'll go from there.

Reply to this Message

Jeff Conrad - 27 May 2005 23:06 GMT

Hi Barb,

> Jeff, You are so kind to help me.

No problem.

> Unfortunately, checking the DAO library did
> not allow me to edit my records. Compiling showed no errors. The donation
[quoted text clipped - 7 lines]
> through a sub-form , the donor ID (primary key) is automatically inserted in
> the record. Does any of this information suggest next steps?

Hummm.....

Try each of these steps on a **backup** copy of the database.
Do each of these *independently* on it's own backup copy and report
back with the results.

1. Open the References Window again and highlight the DAO object
library (it should already be checked at this point). On the right side
of this box there are some Priority buttons. Move the priority of the
DAO just *above* the Microsoft ActiveX Data Objects 2.1 library one.
Compile the code. Any errors? Does the form work now?

2. Go to the References dialog box and see if you can find that Kodak
Image Control in the list. If it is there, check it. Compile the code.
Any errors? Does the form work now?

3. Very, very carefully check the main form, every subform, the queries
that may feed those forms, and the tables themselves involved. Do you
have any fields and/or form controls called Date? If so, Access may be
getting confused about whether you are referring to the Date function,
a table field called Date, or a form control called Date. Let me know.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Barb@CP - 31 May 2005 00:42 GMT

Jeff, I fixed the editing problem. It turned out to be a form properties
issue. Somehow the properties got switched to not allow editing. I switched
them back and it all seems to be working fine now. Thanks so much for your
advice about the references, which got my Date() function to work.

You really helped me out. Thanks!
Barb

> Hi Barb,
>
[quoted text clipped - 35 lines]
> getting confused about whether you are referring to the Date function,
> a table field called Date, or a form control called Date. Let me know.

Reply to this Message

Jeff Conrad - 31 May 2005 03:03 GMT

> Jeff, I fixed the editing problem. It turned out to be a form properties
> issue. Somehow the properties got switched to not allow editing. I switched
> them back and it all seems to be working fine now. Thanks so much for your
> advice about the references, which got my Date() function to work.

Ahh haa, very good Barb!
Great news for sure.

> You really helped me out. Thanks!

I'm glad to have helped Barb.
Good to know you are back on track.
Good luck with your project.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message