473,883 Members | 1,769 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date() function in MS Access 2010

twinnyfo
3,653 Recognized Expert Moderator Specialist
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
17 12426
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
I use the date function in Access 2010 alot, without noticing any problems.

Is the application "Trusted"?
Aug 28 '12 #2
twinnyfo
3,653 Recognized Expert Moderator Specialist
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 Recognized Expert Moderator Expert
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 Recognized Expert Specialist
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 Recognized Expert Moderator Specialist
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 Recognized Expert Moderator Specialist
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.....
Aug 28 '12 #7
zmbd
5,501 Recognized Expert Moderator Expert
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
Aug 28 '12 #8
twinnyfo
3,653 Recognized Expert Moderator Specialist
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,584 Recognized Expert Moderator MVP
Twinny, You may find Converting to Access 2007 or 2010 to be useful in your looking into this issue.
Aug 28 '12 #10

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

Similar topics

4
1945
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 changes to date (lower case vs proper case). I can fix this very temporarily by reconstructing the database (importing all objects to a new mdb) or using the decomplie option to open it. But in either case, once I compile it reverts back to lower case....
3
4425
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. Occasionally though, I need to change the "As Of Date" to some date in the past, and then when I run the queries, they're based on that date in the past. This generally works fine, but, sometimes, the date gets reset to something WAY in the past...
1
4588
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 triggers in the SQL Sever database Many Thanks Phill
5
12435
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 finished working with the database and return it to my pc(which has access 2007) I can't get it open because Access say that the file is not the right format. Does anyone have a way to work around this compatibility problem? So you know I...
2
6719
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 running OK for years in Access 2003; it adds an item selected from an inventory listing to either an existing Sales Order or Purchase Order. For historic reasons Purchase orders are alphanumeric and Sales orders are numeric (long integer). The RowSource...
1
2702
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
7200
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, it was recreating numbers that had already been assigned but deleted. I've read a couple of articles about resetting the autonumber in Access, but I'm wondering if this is the only way to really fix this issue? Has anyone else came up with a work...
1
5273
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 used work in 2003 (see below code). Private Sub engSave_Click() Dim strSql3 As String 'Archive order number and cost data for engine.
2
7334
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 correct method string to turn off the text replace "feature." I am not talking about the "Track Name AutoCorrect Info" method, but rather the method that corresponds to the checkbox if one were to enter Access 2010 and click File, Options,...
1
8501
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 when my associates get new PC's, the applications fail. The error message says to switch from DAO to ADO. This is a little curious since ADO is dying/dead as well. I'm hoping someone can add a little insight/best practice to my situation.
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9796
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11153
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7975
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6002
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4620
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3239
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.