Convert Query SQL To VBA

[jo15765]

Greetings @pbaldy helped me out with syntax in a diff thread on here, but that was just for the SQL - I am now having issues converting it to VBA. This is my Query SQL

SELECT Table1.empID, ConcatRelated('saleItem','[Table1]',"empID='" & [empID] & "'") AS Item
FROM Table1
GROUP BY Table1.empID;

And this is the VBA syntax I have tried...what is the proper way to utilize this in VBA?

DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ' & Chr(34)  & [empID] & Chr(34) & ') AS Item Into Hopscotch FROM Table1 GROUP BY Table1.empID;")

[ranman256]

ID are usually numeric, not string. If this is so, you dont need quotes.

SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ' & [empID] & ') AS Item Into Hopscotch FROM Table1 GROUP BY Table1.empID;"

Tho you can always just concat fields instead of CONCATRELATED.

[jo15765]

how would I use concat and not concat related? The field is short text because it contains alpha numeric characters.

And attempting the syntax you posted gives me the below error
Syntax error in query expression 'ConcatRelated('saleItem', '[Table1]',empID=' & [empID] & ') As Item
INTO Hopscotch From Table1 Group By Table1.empID;

EDIT --
Attached is a sample of my database, this should help me better understand if concat will work

[aytee111]

Ranman's use of the word "concat" is really a short form of the word concatenate. He is saying that you can just concatenate the string (like you've done with empid), instead of using the function called ConcatRelated.

There is an apostrophe missing from the front of [Table1].

[jo15765]

Ranman's use of the word "concat" is really a short form of the word concatenate. He is saying that you can just concatenate the string (like you've done with empid), instead of using the function called ConcatRelated.

There is an apostrophe missing from the front of [Table1].

The missing apostrophe was just a typo on my part. It would not let me copy/paste from the error message box that displayed. I have used Concatenate before in Excel - but what I am after is creating a comma seperated list of each saleItem for the selected empID. Using the database I attached, taking the 3 rows for abc123 and creating ONE row that reads like the below
empID saleItem
abc123 gr1, zz1, fla323

I have had success with the ConcatRelated() function when the field is a numeric field, but I just can not get this to work since it is a text field. Can concatenate() function in vba do what I am after?

[aytee111]

Does this help?
http://allenbrowne.com/func-concat.html

[jo15765]

Does this help?
http://allenbrowne.com/func-concat.html

That's the function I was using, and I have it working in the SQL view of a query - I just for the life of me can not get the syntax down to utilize it in VBA

[aytee111]

Read "The Arguments" step 3. Your quotes are all messed up.

Do you know how to debug VBA? Make a breakpoint and step thru the code, then you can see what each and every field contains at each step.

[jo15765]

Read "The Arguments" step 3. Your quotes are all messed up.

Do you know how to debug VBA? Make a breakpoint and step thru the code, then you can see what each and every field contains at each step.

If I change the syntax to this - it runs error free, but it will insert ALL empID not the selected one from the sub form

DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ''' & [empID] & '''') AS Item Into Hopscotch FROM Table1 GROUP BY Table1.empID;")

And yes - I stepped through my code to ensure the variable [empID] holds the appropriate value

[aytee111]

The reason it is doing it for all EmpIDs is because that is what your SQL is saying, there is no where clause.

It was the strWhere that didn't contain the appropriate value, not the EmpID.

[jo15765]

The reason it is doing it for all EmpIDs is because that is what your SQL is saying, there is no where clause.

It was the strWhere that didn't contain the appropriate value, not the EmpID.

I must be having an issue with the quoting again - i tried this syntax with an added WHERE clause - but it tells me 0 rows to insert

DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ''' & [empID] & '''') AS Item Into Hopscotch FROM Table1
WHERE empID= ' & [empID] & ' GROUP BY Table1.empID;")

[aytee111]

Same as the other, empid needs quotes because it is a text field.

[aytee111]

You are also going to need it to be a totals query with Group By, or else SELECT DISTINCT
Edit: oh, it is!

[jo15765]

Same as the other, empid needs quotes because it is a text field.

I altered the syntax to

DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ''' & [empID] & '''') AS Item Into Hopscotch FROM Table1 
WHERE empID= '' & [empID] & ''")

And access just spins and spins.... Still incorrectly quoted?

[aytee111]

Sigh!
DoCmd.RunSQL ("SELECT [Table1].[empID], ConcatRelated('saleItem','[Table1]','empID = """ & [empID] & """') AS Item INTO [ELS] FROM [Table1] WHERE empID = """ & [empID] & """")