none
Functions not working in Queries RRS feed

  • Question

  • I have an access 2016 accdr that I am putting on different machines. On one machine the queries with Format(), RTrim, etc is working but on another it is not working. Both machines have had the Access 2016 runtime installed. There is not missing links that I can tell. I thought it was possibly due to the wrong reference on the Visual Basics for Applications but I have assigned that to the latest on my machine and still now luck. That dll exists on the machines that it is failing on too. What would cause this and how can I fix it?
    Monday, February 6, 2017 9:41 PM

All replies

  • Don't select the latest version. Select the oldest one you have. Access compiler knows enough to open the latest on the user's machine as well as on yours but if you select the latest it might not be on the user's machine thus causing the functions to blow up.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 6, 2017 10:07 PM
  • Have you tried an Office repair?

    Does your VBA compile without errors?

    Have you compacted and repaired your database?

    FYI, you always develop using the oldest version of Access that the database will be run on.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, February 6, 2017 10:09 PM
  • Yes VBA does compile and I have tried compacting and repairing. This is in the queries it fails. I originally had it at the oldest and it was not working so tried the newest and still did not help. I am using Access 2016 and installing Access 2016 runtime on the other computers. Does it have conflicts with older versions if they exist on the machines? I have had more issues trying to get everything running using Access 2016 then I ever did with 2010. I skipped 2013 and am wondering if I should have skipped 2016.
    Monday, February 6, 2017 10:15 PM
  • You have a Trusted Location defined for the FE?

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, February 6, 2017 10:35 PM
  • What do you mean by FE? The only trusted location that I see is

    C:\...\Microsoft Office\Root\Office16\ACCWIZ\ Access default location: Wizard Databases

    Monday, February 6, 2017 11:11 PM
  • Normally a database is split

    front-end (FE) contains queries, forms, reports, macros, VBA and

    back-end (be) contains tables

    You need to create a Trusted Location for the FE, or if your db isn't split, then the DB file.  Until this is done the VBA will not work.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, February 6, 2017 11:20 PM
  • How do I create a trusted location for a dynamic directory on the users machine? The DB isn't split.

    Tuesday, February 7, 2017 12:12 AM
  • Hi Dalester,

    with following way you can Add Trusted Location.

    Add a trusted location

            
    1. Click File > Options.

    2. Click Trust Center > Trust Center Settings > Trusted Locations.

    3. Click Add new location.

    4. Click Browse to find the folder, select a folder, and then click OK.

    Reference:

    Add, remove, or change a trusted location

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 7, 2017 12:46 AM
    Moderator
  • Grab Gunter's free utility: http://www.accessribbon.de/en/?Trust_Center:Trusted_Locations

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, February 7, 2017 12:48 AM
  • I wouldn't think that would work with an accdr.

    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, February 7, 2017 12:49 AM
  • Which piece are you saying you do not think would work for an accdr? How would I do a trusted location like the following

    c:\users\xxx\appdata\roaming\local\programfolder

    where xxx is the computer users directory.

    Tuesday, February 7, 2017 1:26 AM
  • To Deepak's solution. Sadly, when you run an accdr the Trusted Location Manager is not available.

    You could switch it back to an accdb, and then you'd have access to the menus.

    Or download Gunter's little utility (AddPath2016.exe), drop it into the db's folder and simply double-click on it.  That's it.  Now run your db, everything should be in order now.

    You can also easily create the necessary registry key(s) with vbscript.  This is typically my approach.  As part of my launcher, it executes a vbscript that ensures that a Trusted Location exists for the current path.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Tuesday, February 7, 2017 2:24 AM
  • Something simple to check is whether Sandbox mode is disabled. To use the functions you mentioned one of the modes has to be enabled.

    https://support.office.com/en-us/article/Turn-sandbox-mode-on-or-off-to-disable-macros-8cc7bad8-38c2-4a7a-a604-43e9a7bbc4fb


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 7, 2017 3:17 PM
  • I took the accdb and put it on the machine having issues with the accde or accdr. It worked fine and the functions like rtrim is working. I saved that as an accde on that machine and then it does not work. I even tried with and without the trusted location as mentioned above and still get the error on the accde version. When looking at the accdb it does not show any references missing. Any other ideas?
    Tuesday, February 7, 2017 3:32 PM
  • Hi Dalester,

    I find that if there are multiple versions of Access are installed on that machine then this kind of issues can be occur.

    so there is a conflict with references.

    I also find that if your code is using some other office applications and they have multiple versions or references issue then also this problem can occur.

    so please try to check on your side about this.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 8, 2017 7:34 AM
    Moderator
  • So are you saying if we install the Access 2016 runtime and the user has Outlook 2013 or Word 2013 for instance that this could cause this issue? I know there is not multiple versions of Access installed on the machine.
    Wednesday, February 8, 2017 1:28 PM
  • For fun, perform a manual compile of your accdb and immediately after convert it iver to an accde, and try it again.

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, February 8, 2017 2:17 PM
  • You mean just go into the vba and do a compile or are you saying something else?

    Wednesday, February 8, 2017 5:01 PM
  • Hi Dalester,

    what I mean that I find this issue in some past cases.

    so it can be possible here.

    so I just inform you to check it.

    it is also possible that there are other reasons are liable for this issue.

    other office applications only affect your code if you are using them in your code and having multiple versions installed. otherwise it will not affect the code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 6:19 AM
    Moderator