Using VBA Function in Access Query

Question

I want to write an SQL query using MS access, which calculates a serial number using a VBA Function.

I have a big "samplebasicinformation" table which has lots of foreign keys and want to use the text fields in the foreign tables to create a text-based serial number.

I want the first field of the query to abbreviate a few text fields from other tables, then concatenate them and create this serial number.

In order to abbreviate the text fields I have the following function:

Function GetFirstLetters(rng As String)
Dim arr
Dim I As Long
arr = VBA.Split(rng, " ")
If IsArray(arr) Then
    For I = LBound(arr) To UBound(arr)
        GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
    Next I
Else
    GetFirstLetters = Left(arr, 1)
End If
End Function

I've tried the below SQL to achieve this but failed due to syntax error.

SELECT 
(getfirstletters(select sl.locationname from samplebasicinformation as sbi join samplelocation as sl 
on sbi.samplelocationid = sl.samplelocationid)), 
samplebasicinformationid 
from samplebasicinformation as sbi1

Would anyone be able to offer some advice?

Andre · Accepted Answer · 2021-03-23 12:12:01Z

1

You need to feed a single column to your function.

Your query is rather confusing, I think it is as simple as this:

SELECT 
  getfirstletters(sl.locationname),
  sbi.samplebasicinformationid 
FROM samplebasicinformation as sbi 
  INNER JOIN samplelocation as sl 
  on sbi.samplelocationid = sl.samplelocationid

Note that you need INNER JOIN in Access Sql.

answered Mar 23, 2021 at 12:12

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andre Over a year ago

Obligatory link: youtube.com/watch?v=e3PAKUWm9Oc

user15098571 Over a year ago

Worked first time and thank you for reminding me I haven't watched gladiator recently enough. Thank you Andre!

Collectives™ on Stack Overflow

Using VBA Function in Access Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related