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;
views:
3851answers:
3
+3
A:
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
2009-04-30 21:17:33
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
2009-05-01 07:23:01
@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
2009-05-01 15:05:26
I guess that's why Nz() is available in native Access, but not when you run queries from Excel.
Knox
2009-05-01 20:26:11
+3
A:
I think there is MID() and maybe LEFT() and RIGHT() in Access. Here is a pretty good list of mappings.
JP Alioto
2009-04-30 21:19:10