date()-1 error

[gwog]

Hi I'm using Access 97.

This one really has me stumped.

I have several databases that have been working fine. Until yesterday when my laptop crashed, and IT had to replace my laptop.


Now my queries that have date()-1 don't work anymore.

When I run the query I get this error message:

"Function isn't available in expressions in query expression 'Date()-1'."

It has always worked before. Here is the sql for my query:

SELECT ACTUAL.[Daily SKU_Production Date], Date()-1 AS [DATE]
FROM ACTUAL;

Any ideas what the problem could be are greatly appreciated. It just doesn't make sense to me that these queries have worked fine for the past 10 months - now all the sudden I get a new computer and they don't???

Additional info - the database reside on the same server - not on my individual system. Access 97 is still what my new system uses.

When first got my new system - some of the things like the Find Duplicates Query Wizard did not work. They had to reinstall Access and those now work.

Thanks.

lisa.

 

[jrbarnett]

Hello

Not sure why this doesn't work, but the "proper" way of manipulating dates and times is to use the DateAdd function,

eg

SELECT ACTUAL.[Daily SKU_Production Date], DateAdd ("d", -1, Date()) AS [DATE]
FROM ACTUAL;

Try this and see if it works.

John

 

[CajunCenturion]

I would add to jrbarnett suggestion that you not use [DATE] as the returned fieldname since DATE is a keyword.

With respect to your original question, there are several possibilities that may be in play.

The first thing that I would do is check the Project References, insuring that all are found, and then work out any problems (if any) until you get a clean compile of the project.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[gwog]

Thanks for the suggestions.

The problem is resolved. Apparently there were some references that needed to be updated (in the modules - tools references).

All I did was add new reference, then remove the new reference. And then it worked.

But you are totally right about the naming and proper use, I will make the changes.

Thanks.
Lisa

 

[educate889]

gwog

What references did you change or more importantly, what ones have you selected . . . I am getting the same error with the date function and need a fix also.

 

[gwog]

educate889,

I'm sorry I don't know. My IT group fixed it, and I don't know exactly what they selected.

If you know where I can look in my database that would tell me what was selected I can.

 

[jrbarnett]

educate889

Open a module, then go to Tools -> References and look down each of the references to see if the word "MISSING" is beside them.

These are 99% of the time, the ones that need to be fixed.

John

 

[gwog]

I only have the following checked:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library


Now that jbarnett mentioned the module tools references, I remember IT going in there. Seems like to me - if I remember correctly, what they ended up doing was simply checking a different one hitting ok. Then uchecking it and hitting ok. To force access to refresh. If that makes any sense. I don't suppose it would hurt for you to give it a try educate889 to see if it solves issue for you.

Anyway, sorry I can't be more helpful.