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;
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;
4
I think there is MID() and maybe LEFT() and RIGHT() in Access.
1
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”
1
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
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).