Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110

    Error message: Function is not available

    I have a split database with an accde front end installed on 20 Win 7 computers running Access 2010 run-time. One user is suddenly getting an error when trying to print any report based on a query that includes criteria containing functions such as Left() or Right(). This accde runs fine on all the other computers on our network and the accdb version runs fine on my computer using the full version of Access 2010. I can even open the accde database on the problem computer from my computer and I get no such errors. I've read that MS Office Update KB3085515 can cause that problem, but this update is not installed on that computer. There are no missing references and everything runs fine on the other 19 computers.

    The error message looks like this:

    Function is not available in expressions in query expression '(((tblInvoiceHeader.ActShipDate)>=forms!frmGrower Purchases!StartDate And (tblInvoiceHeader!ActShipDate)<=forms!frmGrowerPur chases!EndDate And ((Left([InvoiceNumber],3)="LRG" And ((tblCustomers.Grower=True) And ((tblInvoiceHeader.Shipped)=True)'



    Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How could this query run for anyone? There is an extraneous [ character in front of InvoiceNumber.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    That's a typo from when I typed it by hand. Actually, it's missing the closing ] character.The SQL statement was generated by Access from design view. The query is actually an update query, but the error message only displays the WHERE clause of the SQL statement.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Should provide attempted SQL statement. Copy/paste from SQLView.

    Are you sure the Office Update was not installed? Have you tried the work around? Or the subsequent update to fix?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    Here's the whole SQL Statement:

    UPDATE tblInvoiceHeader INNER JOIN tblCustomers ON tblInvoiceHeader.CustID = tblCustomers.CustID SET tblCustomers.RevenueThisYr = [RevenueThisYr]+([Subtotal]-[DiscountAmt])
    WHERE (((tblInvoiceHeader.ActShipDate)>=[forms]![frmGrowerPurchases]![StartDate] And (tblInvoiceHeader.ActShipDate)<=[forms]![frmGrowerPurchases]![EndDate]) AND ((tblCustomers.Grower)=True) AND ((Left([InvoiceNumber],3))="LRG") AND ((tblInvoiceHeader.Shipped)=True));

    The update is not installed. I don't know anything about a work around or a subsequent update. This query has worked fine for several years and it works fine on 19 other computers, but now this one user encounters the error with this query and any others with functions in the criteria.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That is apparently not the update I was thinking of. One you reference was back in 2016. How can system not have a 2016 update?

    Here is the one I was thinking of https://support.microsoft.com/en-us/...rs=en-us&ad=us

    Is user able to run query when logged into another computer? Other users okay on this computer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If a function such as Left is causing this issue, maybe it's because InvoiceNumber seems to be an incomplete reference:
    ((Left([InvoiceNumber],3))="LRG")
    You have at least 2 tables involved in this query, but you don't seem to be telling Access which table that field comes from. I can imagine that would cause a not so clear message about not being able to use a function. So why would it work on some pc's? My theory is that different builds and/or Access versions seem to be at the root of other forum issues where the same thing that worked for ages no longer works. When you see the code or sql involved, it often is a wee bit sloppy in terms of references and the like. It's almost as if build updates become more particular about syntax. This would be the first time I've seen only one pc out of a group all running runtime to have such an issue, but I'd start by qualifying which table that field comes from. Actually, you seem to have a couple of other fields with the same sort of referencing.
    Just a thought.
    Last edited by Micron; 07-07-2020 at 08:54 AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, my experience with sudden errors on functions left() and right() is acaused by a missing VBA reference. When a library is not found Access can stop loading the other libs like the ones containing the normal left() or right() functions. Open a VBA window and check Tools -> Reference for missing references.

  9. #9
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    Thanks Micron and NoellaG for the reply. The sql statement I posted is a copy/paste of the sql statement that is displayed when I switch from design view to sql view - it's what Access generated. There are two tables involved, tblCustomers and tblInvoiceHeader. The fields with "incomplete" references are unique to one table or the other, so I guess Access decided the table did not need to be referenced by name. All users are using Access runtime 2010 with SP1 installed to open their own accde front end. The runtime was installed on all computers using the same installation file that I downloaded from Microsoft when we upgraded to Office 2010. I assume that the VBA references selected on my computer in the accdb version are carried over to the accde version. There are no missing references in the accdb version. I have tried creating a new blank database and importing all objects, decompiling, recompiling, compact and repair. If I open the accde on the problem computer from my computer it runs fine, but if I log on to her computer remotely, I get the error. As I mentioned, this user has had not problem literally for years until this issue popped up a week or so ago.

  10. #10
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    Here are the references for this database, if that helps.Click image for larger version. 

Name:	References.JPG 
Views:	25 
Size:	50.0 KB 
ID:	42356

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Whatever the cause is, it must be peculiar to one pc or pc/user combination. If you have connected remotely to that pc, I'm guessing it has nothing to do with the user profile on that pc since you can also raise the error. I'd be examining that pc in depth for variances between it and the others with respect to Office builds and service packs. I suppose I might try that user on a different pc just to be sure, but it would have to be one of the ones that you know that this works on, but it doesn't seem that it will be all that helpful. I suppose too that swapping out that pc with another one is an option, or reinstalling either Office and/or the service pak. I think the issue lies there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    BarCodeWiz is not a standard library. Is it correctly installed on the user's PC?

  13. #13
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    My user is on vacation next week, so I can take a closer look at her PC. BarCodeWiz is an add-on that lets us print bar codes. We've been using it with no problem for years.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I doubt it's relevant as it should not have anything to do with intrinsic Access functions such as Left.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I had problems with the Left() and Right() functions last year.
    I ended up removing the reference to "Microsoft DAO 3.6 Object Library" and adding adding a reference to "Microsoft Office 14.0 Access database engine Object Library".
    If you still have problems, also try adding "Microsoft Office 14.0 Object Library".

    Click image for larger version. 

Name:	References1.png 
Views:	18 
Size:	21.2 KB 
ID:	42362

    IIRC, it had something to do with DAO 3.6 is for Access 2000 and earlier.... (JET 3.6)
    Access 2007 and later uses ACE (JET 4 ?)


    I had Win 7 32 bit / Access 2010 32 bit
    Not I have Win 10 32 bit / Access 2010 32 bit.


    All I can say is, it worked for me.....

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-26-2020, 02:46 AM
  2. Replies: 6
    Last Post: 03-30-2018, 09:18 AM
  3. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  4. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  5. an aggregate function error message
    By newtoAccess in forum Queries
    Replies: 1
    Last Post: 11-27-2010, 05:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums