none
Trim function no longer working RRS feed

  • Question

  • I have an Access 2007 database application that has been working in Windows 7 for several months now. I recently upgraded to Windows  10 and Office 2010. Since that change I cannot use my database program anymore. Simple standard VBA functions like Trim and Ltrim are no longer available. I searched this site and tried all suggestions but nothing seems to work.

    Is there something I'm missing? All suggestions are welcome.

    Saturday, October 17, 2015 1:50 PM

All replies

  • I have an Access 2007 database application that has been working in Windows 7 for several months now. I recently upgraded to Windows  10 and Office 2010. Since that change I cannot use my database program anymore. Simple standard VBA functions like Trim and Ltrim are no longer available. I searched this site and tried all suggestions but nothing seems to work.

    Is there something I'm missing? All suggestions are welcome.

    Hi Bert,

    Check your references!

    Imb.

    • Proposed as answer by André Santo Saturday, October 17, 2015 11:23 PM
    Saturday, October 17, 2015 2:02 PM
  • be sure to set up your Trusted Locations
    Monday, October 19, 2015 8:58 PM
  • Was your Windows 7 OS 64bit and your Office 2007 32 Bit

    and now

    Is your Windows 10 OS 64 bit and your Office 2010 64 Bit?

    If this is the case, I would suggest uninstalling Office 2010 and reinstalling as 32 Bit. Before reinstalling make sure you clean the registry.

    Otherwise I agree with Imb to check your references.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, October 19, 2015 9:06 PM
  • Thank you for your suggestion. 

    Being a inexperienced Access user working on a foreighn language PC I have been looking in all kind of pages in Access to find a hint of an installation that was referencing VBA. Your suggestion of checking references puzzled me since I don't know which references are available and which should be activated in this case. 

    I am still trying to find the right set-up.

    Bert

    Tuesday, October 20, 2015 9:59 AM
  • As explained in the previous reply I am very familiar with the English names of the Access program since al mine are in Dutch. 
    Tuesday, October 20, 2015 10:01 AM
  • Thank you for your reply.

    My original set-up was Windows 7 64 bit and Access 2010 32 bit. The runtime version of that application I tested on a Windows 8.1 64 bit machine with Office 2007 and on Windows 7 32 bit and Office 2007 32 bit. Unfortunately I lost the installation software during an installation of Windows 10 that went wrong resulting in a PC that could not be started anymore so I had to reinstall Windows 7.

    I now am trying to get the Access *.accdb file working again. I tried Windows 10 but are now back on Windows 7 64 bit. With Access 1013 and are now back on Access 2010 32 bit but a in every situation I get the message Function not defined if it hits Len, Right, Mid etc basic VBA instructions.

    There must be be some setting that is missing but the question is I don't which setting. 

    Bert

    Tuesday, October 20, 2015 10:33 AM
  • I found something that may be connected to my problem. Hope someone can explain what happened.

    While trying to rebuild one of my queries containing Left$ and Mid$ that would cause errors I found that in my old file they were written as [left$] and [Mid$] when I left the square bracket off the functions performed as expected.

    I do not know how the brackets got there in the first place and if removing them introduces other problems but I wil check all procedures and correct them accordingly and see if everything wil be as it was before.

    I am working now in Windows 7 64b with Access 2007 32b. If this works out ok I will hopefully be able to upgrade to Windows 10 and Access 2016. 

    Thanks again for your suppport.

    Bert

    Tuesday, October 20, 2015 8:55 PM
  • Hi BertHart,

    Did you mean your issue could be resolved with something like “[left$]”?

    I am not sure where you used this, based on my experience, I usually use left in a query like something below:

    SELECT *
    FROM FilterTable
    where left(Name,1)="O";

    It would be helpful if you could share us a demo code about your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, October 21, 2015 7:50 AM
  • Hi Edward,

    I used it in a query. The file I recoverd after had the left between square brackets and the was not recognized in Access. When I removed the brackets is was accepted. 

    However it was not the only problem, when I compiled the file again after removing the brackets I got other errors of functions that were not recognized for example in "Me.price.Setfocus" an error was given on "setfocus" like Function was not defined.

    I am afraid that I am still missing aomething in the installation, but have not found it sofar.

    Redards,

    Bert

    Wednesday, October 21, 2015 10:53 AM
  • Hi Bert,

    Why did you share us your query?
    “Me.price.Setfocus” could not be used in a query. It is vba code.

    If you want to run sql from vba code, you could use DoCmd.RunSQL.
    # DoCmd.RunSQL Method (Access)
    https://msdn.microsoft.com/en-us/library/office/ff194626.aspx?f=255&MSPPError=-2147217396

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, October 22, 2015 5:23 AM
  • Hi Edward,

    My reply was confusing I think.

    I had problems with VBA code that was not recognized. After removal of the square brackets, see above, my Access database could be used again. However when I tried to compile the VBA code I received errors on other VBA standard code, in this case Setfocus was not recognized in the VBA code.

    The strange thing was that the code that returned the error did work correctly while testing the database functions.

    I am trying to understand what went wrong so I can avoid it in the future.

    Sorry for creating the confusion.

    Regards,

    Bert

    Thursday, October 22, 2015 2:23 PM
  • In the VBA editor window, click on Tools>References. Look for any that are marked MISSING. They are usually older versions of what you have installed on your computer. Uncheck the MISSING ones and replace them with later versions available in the list.

    That is what Imb was telling you, but he should have given more detail. [smile]


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

    • Proposed as answer by Tony---- Friday, October 23, 2015 3:01 AM
    Thursday, October 22, 2015 4:26 PM
  • Thanks Bill,

    I removed one MISSING item but was unaware that I had to look for a replacement. That is my limited knowledge and not Imb's fault. I don't remember what the item was.

    In the meantime I have my database working again in Windows 7 and Access 2010 both 64bit. I tried W10 with Access 2016. It appears that one was working also but then I found out that the developers extensions for 2016 are not available so it will be difficult to have the thing working again in the place where its predecessor was used.

    Thank you all for bringing me back on track.

    Regards,

    Bert

    Thursday, October 22, 2015 8:43 PM
  • That is what Imb was telling you, but he should have given more detail. [smile]

    Hi Bill,

    Was I wrong since I joined this forum? I really thought it was a discussion group.


    Imb.

    Thursday, October 22, 2015 9:19 PM
  • Hi Imb

    I hope I didn't offend you. If I did I'm very sorry.


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

    Thursday, October 22, 2015 9:58 PM
  • Bert

    Can you list the references that are currently checked? You might have some that are not needed.


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

    Thursday, October 22, 2015 10:01 PM
  • Bill

    Below are the references that are checked. The last one I added in the hope that it would slve the errors but forgot to remove it after I found the square brackets.

    ---

    Visual Basic For Applications

    Microsoft Access 15.0 Object Library

    OLE Automation

    Microsoft Office 15.0 Access database engine Object Library

    Microsoft Shell Controls And Automation

    Microsoft 16.0 Object Library

    Microsoft Visual Basic for Applications Extensibility 5.3

    ---

    Thanks for your help!

    Bert

    Friday, October 23, 2015 9:09 AM
  • You can probably uncheck these:

    Microsoft Shell Controls And Automation

    Microsoft 16.0 Object Library

    Microsoft Visual Basic for Applications Extensibility 5.3

    But if you get a message saying they are in use or a compile error put them back in one at a time. Also you should add Microsoft ActiveX Data Objects x.x library. You can choose the lowest version and Access will automatically load the newest one.

    In fact, I bet it's that last one not loaded that is causing the built-in functions to fail.


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

    Friday, October 23, 2015 2:40 PM
  • Bill,

    I followed the suggestions and Am testing how it is behaving now. It seems that everything is still the same. 

    I will continue to test and report the results.

    I noticed that a simple thing, counting the number of records on a page, is not working anymore. I used '' Me.Aantalveld = CurrentDb.TableDefs("TbBedrijven").RecordCount''. Probably something is changed in the newer versions of Access VBA. Sorry for thr Dutch names in the above sample.

    Regards,

    Bert

    Saturday, October 24, 2015 9:17 AM
  • Hi Edward,

    My reply was confusing I think.

    I had problems with VBA code that was not recognized. After removal of the square brackets, see above, my Access database could be used again. However when I tried to compile the VBA code I received errors on other VBA standard code, in this case Setfocus was not recognized in the VBA code.

    The strange thing was that the code that returned the error did work correctly while testing the database functions.

    I am trying to understand what went wrong so I can avoid it in the future.

    Sorry for creating the confusion.

    Regards,

    Bert

    Hi Bert,

    Without seeing your code, I could not test your situation. It would be helpful if you could share us your error code, tell us which line you got error, and details about the error message.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 26, 2015 8:42 AM
  • Hi Edward,

    Apologies for my late reply. I think I found why I had the problems. I used the Office 2007 version that has worked OK and started to move to the newer Office versions. The *.accdb files worked correct in most versions up to Office 2016. I had to add  IPDESIGN.dll to Off 2016 to solve an error message there.

    I used the package solution wizard to be able to distribute the program to the people who used it. That had worked in the past but when I tried it with Office 2013 and later I got the errors I mentioned here before. There was no wizard for 2013 and 2016, so I tried to use the package made with the 2010 wizard.

    After reading the answers given here and spending time on the internet and MS sites I found out that the simple solution was to just rename *.accdb into *.accdr. After that my problem was solved. It is working again as before.

    I am not an ICT expert, just a retired engineer who used access in the past to organize relatively large amounts of data and helped as a volunteer a community paper with their advertising administration using Access. My apologies for asking my dumb questions and I thank you all for your patience and support.

    • Proposed as answer by Tony---- Friday, November 13, 2015 2:10 AM
    Thursday, November 12, 2015 8:23 PM
  • Hi Bert,

    Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.
     
    from the menu in a VBE (module) window: Debug, Compile
    (Alt-F11 to switch to the code window)
     
    Fix any errors on the yellow highlighted lines.
    Add needed references and remove missing references if necessary
    (from the menu: Tools, References...)

    keep compiling until nothing happens (this is good!) -- then Save

    ~~~

    I suspect that because you are now using 64-bit, there is code that needs to be changed such as browsing to files and folders


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Thursday, November 12, 2015 9:16 PM