Hello,
I would like to convert a query that uses a function to vba:
I'm using the following function:
http://allenbrowne.com/func-concat.html
This works well as a query:
but errors out in the vba code below for some reason:
Is it the punctuation issue or something else?
Thanks
I would like to convert a query that uses a function to vba:
I'm using the following function:
http://allenbrowne.com/func-concat.html
This works well as a query:
Code:
SELECT tbl_panel_genes.Panel_Name, ConcatRelated("Gene_Name","tbl_panel_genes","[Panel_Name] = """ & [Panel_Name] & """") AS Expr2
FROM tbl_panel_genes
GROUP BY tbl_panel_genes.Panel_Name, ConcatRelated("Gene_Name","tbl_panel_genes","[Panel_Name] = """ & [Panel_Name] & """")
HAVING (((tbl_panel_genes.Panel_Name)=[Forms]![frm_Samples]![tumor_type]));
but errors out in the vba code below for some reason:
Code:
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strSQL As String
Dim dbValue As Variant
strSQL = "SELECT tbl_panel_genes.Panel_Name, ConcatRelated('Gene_Name','tbl_panel_genes','[Panel_Name] = '" & [Panel_Name] & "') AS Expr2"
strSQL = strSQL & " FROM tbl_panel_genes"
strSQL = strSQL & " GROUP BY tbl_panel_genes.Panel_Name, ConcatRelated('Gene_Name','tbl_panel_genes','[Panel_Name] = '" & [Panel_Name] & "')"
strSQL = strSQL & " HAVING tbl_panel_genes.Panel_Name = '" & [Forms]![frm_Samples]![tumor_type] & "'"
Set rs = db.OpenRecordset(strSQL)
On Error GoTo resultsetError
dbValue = rs!Panel_Name
Debug.Print dbValue
MsgBox dbValue, vbOKOnly, "RS VALUE"
resultsetError:
MsgBox "Error Retrieving value from database", vbOKOnly, "Database Error"
Is it the punctuation issue or something else?
Thanks