how to write query with function inside it in access vba?

Question

Lets suppose, vba code has

Dim SQL as String
SQL ="select * from table"

I found function that helps to combine values from related rows into a single concatenated string value.But when i implemented to my code it didn't work because double quotation inside query creating errors.What could be best format to make syntax valid in vba.The function is given below and link is (Combine values from related rows into a single concatenated string value).

SELECT
i.N_ID,
i.F_Name,
i.L_Name,
ConcatRelated(
    "Course_ID",
    "tbl_Courses",
    "N_ID = '" & [N_ID] & "'"
    ) AS Course_IDs
FROM tbl_Instructors AS i;

Use "" "" to escape string format – Techie Jul 27 '18 at 06:14 — Techie, Jul 27 '18 at 06:14

score 0 · Answer 1 · answered Jul 27 '18 at 06:58

0

Most likely, your ID is numeric, not text, thus no single-quotes:

SELECT
i.N_ID,
i.F_Name,
i.L_Name,
ConcatRelated(
    "Course_ID",
    "tbl_Courses",
    "N_ID = " & [N_ID] & ""
    ) AS Course_IDs
FROM tbl_Instructors AS i;

answered Jul 27 '18 at 06:58

Gustav

53,498
7
29
55

how to write query with function inside it in access vba?

1 Answers1