Concatenate-Access 2003

[Broadbonian]

I have tried to use Duane Hookom's generic function query to get the
EQUIPMENT field to concatenate, but can't seem to get it right.
Here is my original query- SELECT CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT
FROM CreateSchEqp
WHERE (((CreateSchEqp.EQUIPMENT)<>"BLANK"));
Can you help?

 

[John Spencer]

SELECT DISTINCT CreateSchEqp.JOB
, Concatenate("SELECT EQUIPMENT FROM CreateSchEqp WHERE Job ='" & [Job]
& "' AND Equipment <> 'Blank' ORDER By Equipment")
, CreateSchEqp.BEGDT
, CreateSchEqp.ENDDT
FROM CreateSchEqp


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Broadbonian]

I opened the query in SQL view, pasted your SQL, got this error"Undefined
function ' Concatenate' in expression". Data is from a make-table query, and
data type is binary. New to all this, appreciate the help
--
Darc

SELECT DISTINCT CreateSchEqp.JOB
, Concatenate("SELECT EQUIPMENT FROM CreateSchEqp WHERE Job ='" & [Job]
& "' AND Equipment <> 'Blank' ORDER By Equipment")
, CreateSchEqp.BEGDT
, CreateSchEqp.ENDDT
FROM CreateSchEqp


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have tried to use Duane Hookom's generic function query to get the
EQUIPMENT field to concatenate, but can't seem to get it right.
Here is my original query- SELECT CreateSchEqp.JOB, CreateSchEqp.EQUIPMENT,
CreateSchEqp.BEGDT, CreateSchEqp.ENDDT
FROM CreateSchEqp
WHERE (((CreateSchEqp.EQUIPMENT)<>"BLANK"));
Can you help?

 

[John Spencer]

You did say you were trying to use Duane Hookom's generic function query.

Did you copy the vba function named Concatenate to your database?

Did you save the vba module with a name other than Concatenate? Hint: I
prefix the module name with mod_ and never use that prefix anywhere else in
the database.

Did you make the specified changes to the function? There are some lines that
need to be excluded (commented out) and others that need to be included. An
apostrophe (') at the beginning of a line of code comments out the line of code.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Broadbonian]

Sorry-I don't know anything about modules. Thought I just had to copy your
SQL into my query. I copied Duane's module code, and renamed mod_con. Now
your SQL works. Thanks so much. I would have been here for hours.