CODEWITHSUNDEEP
ms-access
CoderDennis
CoderDennis

Reputation: 13837

Is there an equivalent to the SUBSTRING function in MS Access SQL?

I want to do something like this within an MS Access query, but SUBSTRING is an undefined function.

SELECT DISTINCT SUBSTRING(LastName, 1, 1)
FROM Authors;

Upvotes: 27

Views: 136784

Answers (5)

Ernestico
Ernestico

Reputation: 1

I used an update query as follows: I added to my access table one empty column for the string element I needed. Then I filled the new column with an update query with this logic in the "UpdateTo" line: "Mid([TABLE].[FIELD],3,1)" as I needed exactly the 3 character of the field. The preceding answers took me here (thanks).

Upvotes: 0

JP Alioto
JP Alioto

Reputation: 45117

I think there is MID() and maybe LEFT() and RIGHT() in Access.

Upvotes: 6

webzy
webzy

Reputation: 368

I have worked alot with msaccess vba. I think you are looking for MID function

example 

    dim myReturn as string
    myreturn = mid("bonjour tout le monde",9,4)

will give you back the value "tout"

Upvotes: 6

Sverre
Sverre

Reputation: 21

I couldn't find an off-the-shelf module that added this function, so I wrote one:

In Access, go to the Database Tools ribbon, in the Macro area click into Visual Basic. In the top left Project area, right click the name of your file and select Insert -> Module. In the module paste this:

Public Function Substring_Index(strWord As String, strDelim As String, intCount As Integer) As String

Substring_Index = delims

start = 0
test = ""

For i = 1 To intCount
    oldstart = start + 1
    start = InStr(oldstart, strWord, strDelim)
    Substring_Index = Mid(strWord, oldstart, start - oldstart)
Next i

End Function

Save the module as module1 (the default). You can now use statements like:

SELECT Substring_Index([fieldname],",",2) FROM table

Upvotes: 2

Tomalak
Tomalak

Reputation: 338158

You can use the VBA string functions (as @onedaywhen points out in the comments, they are not really the VBA functions, but their equivalents from the MS Jet libraries. As far as function signatures go, they are called and work the same, even though the actual presence of MS Access is not required for them to be available.):

SELECT DISTINCT Left(LastName, 1)
FROM Authors;

SELECT DISTINCT Mid(LastName, 1, 1)
FROM Authors;

Upvotes: 39

Related Questions