25

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;
cgp
  • 41,026
  • 12
  • 101
  • 131
CoderDennis
  • 13,642
  • 9
  • 69
  • 105

5 Answers5

36

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;
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • 3
    Picky point: the dlls involved are (I belive) Expsrv.dll and vbajet32.dll, which MS documentation refers to collectively as Jet Expression Services. So I think we can consider them as being native ACE/Jet expressions, rather than 'VBA functions' in context. Note some expressions work differently in ACE/Jet than their equivalent VBA functions e.g. IIF(): VBA always evaluates both true and false clauses, whereas ACE/Jet does not. – onedaywhen May 01 '09 at 07:23
  • @onedaywhen: Thanks for clarifying. I was hesitant of writing "VBA functions", but I didn't know what to write instead. In case of the string functions mentioned they are obviously equivalent. OTOH - I think you can even use user-defined VBA functions in an Access query, but this may be Access specific. – Tomalak May 01 '09 at 15:05
  • 2
    I guess that's why Nz() is available in native Access, but not when you run queries from Excel. – Knox May 01 '09 at 20:26
  • 1
    Access is a host for VBA. It really is VBA. VBAjet.dll is just a loader for VBA. The version I just looked at had only two functions: Get Expression Service, and Load Expression Service. If Get finds a VBA / VB dll already loaded in memory, it is used. If not, Access Loads a copy. Although the loader is called vbajet32, Access is actually the VBA host, and has the hooks to hook VBA into Jet. – david Apr 12 '16 at 03:48
6

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

l0pan
  • 476
  • 7
  • 11
JP Alioto
  • 44,864
  • 6
  • 88
  • 112
4

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"

webzy
  • 338
  • 3
  • 12
  • 1
    Welcome to StackOverflow! Your answer is correct, but it probably wont get any votes since it doesn't provide any new information that isn't already in the accepted answer. – CoderDennis Sep 02 '14 at 19:15
2

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
Furqan Safdar
  • 16,260
  • 13
  • 59
  • 93
Sverre
  • 21
  • 1
0

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).

Ernestico
  • 1
  • 1