Date() function in MS Access 2010

[twinnyfo]

Hey coders,

Has anyone heard anything about the Date() function no longer working (or working differently in MS Access 2010? I have an MS Access 2007 DB that has worked perfectly, however, we have a bunch of machines running on the same front end, and the machine with MS Access 2010 generates an error that the function cannot be found in the line of code that uses that function.

Any ideas?

This is the line of the query that generates the error:

(((tblProjects.ReleaseDate)<=Date()-60) AND ((Sum(IIf([Status]=4,0,1)))=0))

[TheSmileyCoder]

I use the date function in Access 2010 alot, without noticing any problems.

Is the application "Trusted"?

[twinnyfo]

Our Network Nazis may have changed that for the new systems. I'll give a look and see what I can find. Thanks!

[zmbd]

TwinnyFo:
Did I read that correctly???
You have a single front-end that multple users are opening from the same file?
You have v2007 and v2010 opening that same file?

Ouch. I think you may have a file-locking issue

What you've posted I suspect is only a part of the actual code: Please post the err.num , err.desciption that your V2010 is tossing your way.

-z

[Seth Schrock]

I have had the same issue in Access 2010 where I have the default value of a textbox being Date(). How I ended up fixing it was to create a new database and copy and paste everything into the new one, including the form that was having the issue. It worked fine. It seems like it might be a bug where the function just stopped working. I had even tried using the Now() function and it didn't work until I made the new database.

[twinnyfo]

Z--

No, it's not the same file. The same FE is downloaded locally, then opened. Experience taught me not to do otherwise.....

Here's the latest: The file is trusted; no apparent security issues. But, here's the real kicker, when I download a non-compiled version of the FE to the 2010 machine and compile it there, I have no issues. But, if I compile it on my 2007 machine, I get the

"Function is not available in expressions in query expression '(((tblProjects .ReleaseDate)<= Date()-60) AND ((Sum(IIf([Status]=4,0,1)))=0)'.

as an error. So, it could either be the Date() or IIf() or the Sum() functions..... Either way, it's not like it's some sort of strange user created function that gets all jacked up 'n' all. These are pretty standard features.....

What is even STRANGER, is that this db worked fine YESTERDAY--same code, compiled on same machine. Are there some 2007-2010 compatibility issues that would cause this?

I've been trouble shooting this all day, but to no avail....

Any guesses?

[twinnyfo]

OK.... More gouge. When I recreate this query, and use the Date() function in the criteria, the db tells me

"The function you entered can't be used in this expression."

Supplementary reason given:

"You may have used an aggregate function, such as Count, in a design grid or in a calculated control or field."

What? Did MS Change the rules for their queries all of a sudden? This is really whacked, as I have been using functions in criteria like this for years.

Grrrrrrrrrrrrrr rrrrrr.....

[zmbd]

I've read where the references vba\tools\refer ences can get buggered when the database is used between the two 2007/2010.
I'd double check those first - see if anything thing is marked as missing on the 2010 machine.
-z

[twinnyfo]

Z- Good thought.... all referenced references present. I'm in the processs of trying Seth's suggestion. Believe it or not (Oh my!) I've done this before for other DB stoppage issues. A total copy and paste of all objects sometimes does the trick, so we will see how this goes......

[NeoPa]

Twinny, You may find Converting to Access 2007 or 2010 to be useful in your looking into this issue.

[TheSmileyCoder]

I would also suggest decompiling/recompiling if you have not allready tried that.

[twinnyfo]

NeoPa, since I only have one machine using 2010, conversion to 2010 is not my main concern right now. It will be whenever the rest of our machine do the switch (DoD snail's pace upgrades).

So, here is what I did: I created a completely new blank database and imported all objects into the db and relinked all external tables. Thanks to Smiley, I was able to fix any broken queries that weren't seeing properly because of the transfer, since some queries were dependent on others. Debugged, no errors, copied FE to the new 2010 machine and the thing works like a charm!

Yes, MS Access does, in fact, have a mind of its own.

Seth, thanks for the tip. I've actually had to do this in the past with other dbs that just "stopped working". Yet another "tool" for my kit.......

Thanks all!

[Seth Schrock]

It was one of the most frustrating things that I have had to deal with because I knew that it had been working the day before and I knew I had the syntax correct, but it just stopped working. I couldn't find anything online to help me, so I just started copying things over one at a time waiting for it to break and it never did. I then tried copying everything in groups and it still worked.

Anyway, glad it worked for you.

[TheSmileyCoder]

While the suggestion by Seth is good and valid I would usually try and decompile/recompile first, since there is alot of information that can't be imported into a new database, like startupform and other settings.

[twinnyfo]

Smiley,

Yes, I recompile just prior to every publication of changes.

I agree that Seth's "solution" is more of a workaround than anything else. However, for those who come across similar quirks, I thought it wise to list it as a possible workaround.