Access 2007 Error: "Undefined function '[InStr$]' in expression"
Posted on 2011-05-11
I am not a heavy Access user. We have a database that I created that we use only once a year to track property owner's costs for our sidewalk program. We have never had significant problems from year to year until this year.
In a query, we are extracting data from a propety owner database. The owner data is in a field where the last name, first name, and mi are all in one field. In order to extract the last name and first name from this field we are using the [InStr] function to calculate the total number of characters in the field and then in additional fields; find the spaces so that we can pull out the appropriate data to populate letters to the property owners.
When we open the reports (with the letters to the property owners) we are getting this error:
"Undefined function '[InStr$]' in expression"
After doing some searching online, I found a blog that determined that this is a Visual Basic error and instrcuted me to:
-Open any code module, then select Tools | References from the menu bar. Examine all of the selected references.
-If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them.
-If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected.
I folowed this procedure. There were no "MISSING" references and I could only uncheck the "OLE Automation" and "Microsoft Active X Data Objects 2.7 Library" ("Visual Basic Application" and Microsoft Access 12.0 OLE Library" returned "Can't remove control or reference, in use"). This however did not solve my problem and it return the same [InStr] error.
I am kind of at the limit of what i can do comfortably and I am hoping that there is a solution for thsi problem. I have to get these letters out as sson as possible. Any help would be greatly appreciated!!