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

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

I think there is MID   and maybe LEFT   and RIGHT   in Access

[ms-access] Is there an equivalent to the SUBSTRING function in MS Access SQL?

Examples related to ms-access