Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


'Function is not available' message

Posted on 2014-01-13
Medium Priority
Last Modified: 2014-02-03
I am in the middle of replacing several Access 2003 applications with Access 2010 versions.  None of the users have MS Access installed so I package and deploy these applications along with Access 2010 runtime.  I first remove all of the old 2003 applications and access 2003 runtime then install the new applications.   The workstations are a mixture of Windows 7 and Windows XP.
   On one of the Windows XP workstations I get the following message when trying to run some options in the new application:

Function is not available in expressions in Query expression 'Date('

  The new application runs fine on other XP workstations.  Any ideas on what the problem could be and how to fix it?
Question by:dsoderstrom
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 58
ID: 39776541
<< Any ideas on what the problem could be and how to fix it? >>

 You have broken references.  Open a code module, go to tools/reference and fix any listed as MISSING or BROKEN.

LVL 52

Expert Comment

ID: 39776557

Is the reference to

Microsoft Jet and Replication Objects Library ticked?


Author Comment

ID: 39776576
MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 39776614
I came up with some code that will allow me to display the references from within the application.  I will add a command button in the application to run this code and display the references and then redeploy it to the workstation causing the problem.  Then I should be able to see if any of the required references are missing.
   If it turns out that one of the references is missing, how do I fix it?  Is it as simple as copying the the .dll source file for the reference to the proper location on the computer or won't that work?
LVL 48

Expert Comment

by:Dale Fye
ID: 39776681
If you can do the packaging of the file on an XP workstation rather than a Win 7 workstation, you might be able to avoid the references issues.  References generally scale upwards well, but not backwards.
LVL 58
ID: 39776735
<<MS Access is not installed on the computer, only access runtime.  I can not open the database to look at references. >>

  Then you either need to look at it on a station with a full version, or add code to the app to list the references and what's broken at start-up.  Then go from there.

But something is broken.

 I would suggest in addition to what Dale said, taking the DB and under a full version, eliminate all possible reference (uncheck, try a compile) and get down to the minimum set of references.


Author Comment

ID: 39777131
Had to go out of the office on a project so will have to get back to this tomorrow.
One question though.  Would it really help to look at this database on another computer?  Wouldn't a missing reference problem be specific to this computer.  As mentioned previously, the application works fine on other XP computers.
I did add code to the app to list the references so will redeploy the app to the problem computer and check the references when I get back to the office.
LVL 58
ID: 39777185
<<One question though.  Would it really help to look at this database on another computer? >>

  Yes and no.   yes in that you can eliminate some references that may not be used or spot something that needs to be installed that your using (like vbSendMail.DLL).

<<Wouldn't a missing reference problem be specific to this computer. >>

 Yes, that's true.

<< did add code to the app to list the references so will redeploy the app to the problem computer and check the references when I get back to the office. >>

 That's the ticket.  Make sure you include the .IsBroken proeprty so you'll know which reference(s) it is.


Author Comment

ID: 39780035
1)  I added the Microsoft Jet and Replication Objects  reference as suggested above.
2)  Added a command button to the application for checking references.
3)  Redeployed the application to the problem computer.
4)  Checked the references.  All of them appeared and there were no broken ones.
5)  Still got the same error message.

Pondering my next step.

Author Comment

ID: 39780382
One more thing ......

I also tried packaging the application from a Windows XP computer.  Same problem
LVL 58
ID: 39780905
<<1)  I added the Microsoft Jet and Replication Objects  reference as suggested above.>>

  You don't need these typically.  uncheck the reference, do a compile.  If you get a compile with no errors, then you don't need the reference.

<<Pondering my next step. >>

Two possible things depending on the libs your using, do you use DAO and/or ADO?

For DAO, you may need to re-register DAO on the target machine.  From a command prompt, do:

regsvr32 -u "c:\program files\common files\microsoft shared\dao\dao360.dll"

Followed by:

regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"

regsvr32 is found in C:\Windows\System32

If your on a 64 bit OS, you will need to use the one in C:\Windows\sysWOW64  assuming you have the 32 bit edition of Office as well (that also may be part of the problem).

With ADO, there was a bug a while back which you might be tripping over (don't think so because you moving from old to new, but it doesn't hurt to double check):

An ADO-based application that is compiled in Windows 7 SP1 or in Windows Server 2008 R2 SP1 does not run in earlier versions of Windows


Author Comment

ID: 39788378
Sorry for the slow response.  Have been away from the office working on another project.
In response to your question Jim, I am using DAO.  I will try your suggestions regarding that next Tuesday when I get back to that office.  Thank You

Author Comment

ID: 39803919
Finally got a chance to try re-registering DAO on one of the computers I was having a problem with.
Still get the same error message (see original post).
This error message seems to refer to the Date() function.  I use this extensively through the applications to return the current date.
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39804168
In some way, it's a broken reference.  This is not unusual and is a long standing problem:

ACC97: Error Message: Function Isn't Available in Expressions in Query Expression

 let me think about this a bit...


Assisted Solution

dsoderstrom earned 0 total points
ID: 39815991
I found a way to solve my problem.  It's probably not very elegant and should not be necessary but what I did is add the code shown below to the startup routine of my applications.  All it does is remove the last reference in the list and re-add it.  It doesn't seem to matter which reference I refresh and I only need to do one of them.
Jim, I'm awarding the points to you because you kept me on the right track regarding a reference problem and offered a lot of help.  Thank you.

Private Sub RefreshRef()

    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim strPath As String
    On Error Resume Next
    'Count the number of references already referenced
    intCount = Access.References.count
    'Get the last reference, remove it, then add it back.
    Set loRef = Access.References(intCount)
    With loRef
        strPath = .FullPath
        With Access.References
            .Remove loRef
            .AddFromFile strPath
        End With
    End With
End Sub
LVL 58
ID: 39816001
First, I'm sorry I didn't get back to you.  I've been a bit swamped lately.

Second, I've very happy to hear you figured out a solution!

and third, because of that, I'm going to ask you to close this a bit differently.

I want you to accept your own comment as the solution.  You can if you want give me an assist for pointing you in the right direction, but the solution is certainly yours.


Author Closing Comment

ID: 39829063
I accepted my own comment as the solution because it was what ultimately solved the problem.  I appreciate the assistance given to me by others that led me towards this solution.

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question