473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date() function in MS Access 2010

twinnyfo
3,653 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. (((tblProjects.ReleaseDate)<=Date()-60) AND ((Sum(IIf([Status]=4,0,1)))=0))
  2.  
Aug 28 '12 #1

✓ answered by 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.

17 12352
TheSmileyCoder
2,322 Expert Mod 2GB
I use the date function in Access 2010 alot, without noticing any problems.

Is the application "Trusted"?
Aug 28 '12 #2
twinnyfo
3,653 Expert Mod 2GB
Our Network Nazis may have changed that for the new systems. I'll give a look and see what I can find. Thanks!
Aug 28 '12 #3
zmbd
5,501 Expert Mod 4TB
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
Aug 28 '12 #4
Seth Schrock
2,965 Expert 2GB
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.
Aug 28 '12 #5
twinnyfo
3,653 Expert Mod 2GB
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?
Aug 28 '12 #6
twinnyfo
3,653 Expert Mod 2GB
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.

Grrrrrrrrrrrrrrrrrrrr.....
Aug 28 '12 #7
zmbd
5,501 Expert Mod 4TB
I've read where the references vba\tools\references 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
Aug 28 '12 #8
twinnyfo
3,653 Expert Mod 2GB
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......
Aug 28 '12 #9
NeoPa
32,556 Expert Mod 16PB
Twinny, You may find Converting to Access 2007 or 2010 to be useful in your looking into this issue.
Aug 28 '12 #10
TheSmileyCoder
2,322 Expert Mod 2GB
I would also suggest decompiling/recompiling if you have not allready tried that.
Aug 29 '12 #11
twinnyfo
3,653 Expert Mod 2GB
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!
Aug 29 '12 #12
Seth Schrock
2,965 Expert 2GB
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.
Aug 29 '12 #13
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Aug 29 '12 #14
twinnyfo
3,653 Expert Mod 2GB
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.
Aug 30 '12 #15
NeoPa
32,556 Expert Mod 16PB
I'm not aware of a decompile option Smiley. Is that something we should know about?

Twinny, A very basic workaround, but nevertheless what the OP found most helpful in this situation and others may too. That's the main point of the 'Best Answer' so I'm happy to leave it that way (Believe me - I'd reset it if I felt it had no value).

Of course, that never means that it's pointless to try other approaches, nor that any other suggestions were wasted. Most of these threads are read thousands of times by different people so what suits the OP may not suit all others. Every sensible suggestion can prove very helpful.
Aug 30 '12 #16
TheSmileyCoder
2,322 Expert Mod 2GB
NeoPa:
I'm not aware of a decompile option Smiley. Is that something we should know about?
I dont want to clutter up this thread more, and the information might be usefull in other situations so I wrote up a article, which can be found here:
How to Decompile an Access Application Or How to do a Full Fresh Compile
Aug 30 '12 #17
NeoPa
32,556 Expert Mod 16PB
Smiley:
I dont want to clutter up this thread more, and the information might be usefull in other situations so I wrote up a article, which can be found ...
Perfectly handled :-)

Thanks for that. I've added the link to my database as it should prove very handy. I think I'll try that on my current project tomorrow, but I won't go into that here ;-)
Aug 31 '12 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Christine | last post by:
I am having the strangest problem. I use the Date function in several of my forms and modules in an Access 2000 mdb. Lately, wherever in my code (in this one mdb) I use the Date function, it...
3
by: murch.alexander | last post by:
I made a simple public function to set and return a date value (see below). I have a number of queries that call up the function to get the "As Of Date," which is typically set to today's date....
1
by: phill86 | last post by:
Hi, I have an access 2010 database that I want to convert to an SQL Server database and I need to know if the data macros in access will still work in the SQL database or will i have to create...
5
by: jaad | last post by:
Hello, I have a database that was written in access 2007 on my pc. I wanted to work off site with the database so I uploaded it onto my laptop which is loaded with access 2010 beta. When I...
2
by: sierra7 | last post by:
It seems Access 2010 is associating an 'input mask' or field type with a combo box when a form is opened, even though there is no Format setting on the control. I have a form which has been...
1
by: zolamdlankomo | last post by:
(SMALLDATETIME,GETDATE(),20) ...i want the date to be 2010-02-02. wen i run the statement above it gives mt todays dat bt i want 2010-02-02
2
by: dougancil | last post by:
I have a user who had deleted some records from a database today using Access 2010. They have an ID field that's autonumbered. They have No Duplicates allowed. When they created a new record today,...
1
by: Alan Yim | last post by:
Hi folks, My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question...
2
by: Bill Boord | last post by:
I need to be able to shut off the AutoCorrect "feature" within Access 2010 code. I have utilized Application.SetOption with method strings for other startup requirements, but I cannot seem to find a...
1
dsatino
by: dsatino | last post by:
I have numerous applications built in Access 2000/2003 that all use ODBCdirect workspaces to access various non-Access databases. Unfortunately, ODBCdirect is 'no longer supported'in Access 2010 and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.