Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

i am trying to write a query using left function in access to take only the first 3 characters of a field.

Is there any alternative method for performing the same process without using left

Usage of left function shows a compile. error all of a sudden without any reason. If i copy the table and query to a new database it works fine for a while before the error comes again. This happens only on the usage of Left function.

share|improve this question
up vote 3 down vote accepted

you can use mid function:

Mid (Field, 1, 3)
share|improve this answer

The compile error is showing up because you have a missing reference. Open any module and check the References.

share|improve this answer

You can always try Mid

Mid([field1],1,3)
share|improve this answer

It sounds very like you have a problem with your references. Look for any references marked "MISSING". Also try to delete Visual Basic for Applications, it won't allow this, but it sometimes corrects the problem. Finally, check the details of Visual Basic for Applications and make sure that is available in the stated location. Any alternative to Left will be affected by this problem.

This problem is frequently associated with a missing reference that you would not think had anything to do with Left.

share|improve this answer

Run the following code and report back as to the results. Also tell us what version of Access you are running.

Sub ViewReferenceDetails()

Dim ref As Reference

    For Each ref In Access.References
        Debug.Print ref.Name & " - " & ref.Major & "." & ref.Minor & " - " & ref.FullPath
    Next ref

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                       <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\n                </div>\n            </div>\n\n            <div id=\"copyright\">\n                site design / logo &#169; 2016 Stack Exchange Inc; user contributions licensed under <a href=\"https://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\n            </div>\n            <div id=\"svnrev\">\n                rev 2016.2.12.3248\n            </div>\n            \n        </div>\n    </div>\n    <noscript>\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\n    </noscript>\n\n   "], "copyright__copyright__copyright": ["t back as to the results. Also tell us what version of Access you are running.\n\nSub ViewReferenceDetails()\n\nDim ref As Reference\n\n    For Each ref In Access.References\n        Debug.Print ref.Name & \" - \" & ref.Major & \".\" & ref.Minor & \" - \" & ref.FullPath\n    Next ref\n\nEnd Sub\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}