converting query with function to vba (1 Viewer)

MilaK

Registered User.
Local time
Today, 10:02
Joined
Feb 9, 2015
Messages
285
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:

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
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
15,364
Tell us about the error.

You should always do a
Code:
Debug.Print  strSQL

as a test before trying to run/execute the sql.

It will show you what/how Access has understood and rendered your statement.

Good luck.
 

MilaK

Registered User.
Local time
Today, 10:02
Joined
Feb 9, 2015
Messages
285
Here is my latest attempt:

Code:
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] & "'"

it debug.prints with error 3075 missing operator:

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 = 'Lung'

Please help, it's driving me crazy!
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Jan 23, 2006
Messages
15,364
Can you post a copy of the database (zip format) ? Remove anything private first.
 

Users who are viewing this thread

Top Bottom