You are here:

Using MS Access/Group By and Concactenate (cont..)

Advertisement


Question
QUESTION: Hi Scott!

This is a continuation from my prior question/issue: You asked what error message(s) i get: you name it-- "Invalid Syntex" is the most common one.  I think I need to start from the VERY beginning:
1) You said to use the Group By function: is this the same as the "TOTAL" button in design view of a query or is this something different?
2) I apologize for being so elementary, but I do not get what part of the concactenate function to actually paste into my query and where exactly do I paste it? I have tried doing this in the "zoom" box in query, but when I replace your example fields with my actual fields, is when I start getting errors---I have yielded nothing all day from this.... I just don't know the basics of what you are suggesting

Thank you for taking me back a few levels here.

-Kara

ANSWER: 1) Yes, The Sigma icon, titled Totals, is used to create a Gropup By query and adds a Group By row

2) You don't paste in any part of that code to your query. Once you paste the code into a global module it creates a user defined function that is used just like any function. So you add a column to your query something like:

Tested: fConcatFlds("tablename","SampleName","Type Tested","String",[TypeReceived])

The article explains this.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott:
Okay...I am now trying to understand the function aspect of your answer in general; I have never used functions and I am still totally lost as to what they are and how I am to use one from an external source in an internal query that I just made???? Am I possibly missing part of the article yopu are referencing? All I see is the actual code....I tried doing what you exemplified in a new column and I got "undefined function" error.....

I am sorry to prolong this, I truly want to understand what yoiy are telling me; i am just missing a preliminary lesson i think.
Thank you for your patience with me!!!

-Kara

ANSWER: No need to apologize. Until I know what you don't know, I can't fully explain.

I'm sure you HAVE used functions before. Have you ever used Now() to get the current data and time? That's a function. Or have you ever use Sum() to total values? Again that's a function. A function is Access is something that returns a value. There are built In and user defined functions. Now() and Sum() are built in functions and there are dozens of them. But, Access also allows you to create your own functions that perform tasks that Access didn't build in. Such a function is fConcatFlds as listed in that article.

To create a user defined function (UDF), you need to open the Access Visual Basic Environment (VBE). You can do this by pressing Ctrl+G. You can go to the Insert menu and select Insert Module. This opens a blank Global Module. With the cursor in this new module, you go back to Insert and this time you will see an option to insert a Procedure. Choose that and you get a dialog box that allows you to name the procedure. You also select the type (in this case, Function) and you want it to be Public.

But in this case, since you are copying code from someplace. You just copy from the first line (Public function fConcatFlds...) to the last line (End Function) into the global module. Once you do this and save the module, you can use that function in the same way you use any built in function.

The reason you are getting an undefined function error, is because you did not copy the code into a global module. so it can't be referenced by your query.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Hi Scott--- me, again!

Okay: first off: THANK YOU for explaining the function of the functions; very helpful; I get that part now.  So, I copied the code into the new module; keeping the same naming convention and I STILL get invalid function error???  I copied/pasted from just under "code start" to "end code" Am I supposed to delete out the green text portion once in the module? I am not sure if the error is stemmed from the wrong setup in my query column or from what I pasted into the module.....Any ideas?
I know I am getting closer.....Thank you again for your guidance and prolonged patience!
-Kara

Answer
Hmm, Are you sure you saved the module? The Module name doesn't matter. But it needs to be saved. Can you show me everything that is in the module?

Don't worry about the green parts.

If you type:
? fConcatFlds(

in the Immediate window of the VBE, does Intellisense list the parameters?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.