CODEWITHSUNDEEP
sqlms-access
tksy
tksy

Reputation: 3529

Access. Alternative for left function

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.

Upvotes: 0

Views: 958

Answers (5)

Tony Toews
Tony Toews

Reputation: 7882

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

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91366

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.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166406

You can always try Mid

Mid([field1],1,3)

Upvotes: 2

Wael Dalloul
Wael Dalloul

Reputation: 23024

you can use mid function:

Mid (Field, 1, 3)

Upvotes: 3

Raj More
Raj More

Reputation: 48014

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

Upvotes: 3

Related Questions