Okay the 2 parameters are the date values you supply at runtime, namely:
[Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
[Enter Finish Date: (mm/dd/yyyy) ]
The problem occurs because you are passing the "test2" query into the
function. It tries to open the recordset, and cannot handle the parameters.
You may be able to solve the problem by substutiting the name of a table
instead of test2, though that would return all the auditors for the project,
not just those that worked on it in the specified period. There are a couple
of other alternatives, but they also require writing some code, so that's
probably not useful.
You may be able to simpilfy that into a single query, as below:
SELECT tblMASTERFILE.[Project#],
tblProjectCodes.LayoutID,
tblProjectCodes.Description,
fConcatFld("MyTable","Project#","Auditor","string",[Project#]) AS Auditors,
Sum(tblMASTERFILE.Time) AS SumOfTime
FROM tblUserIDs INNER JOIN (tblProjectCodes INNER JOIN tblMASTERFILE
ON tblProjectCodes.[Project#] = tblMASTERFILE.[Project#])
ON tblUserIDs.[ID#] = tblMASTERFILE.[ID#]
WHERE tblMASTERFILE.Date Between
[Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
GROUP BY tblMASTERFILE.[Project#],
tblProjectCodes.LayoutID,
tblProjectCodes.[Description],
ORDER BY tblProjectCodes.LayoutID;
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
The Uke said:
Sorry Allen did i say that im not a programmer here are I don't if i
shoulda
included this last time but here are the to sql statements first is the
main
query
SELECT tblMASTERFILE.[Project#], tblProjectCodes.LayoutID,
tblProjectCodes.Description, tblUserIDs.Auditor, Sum(tblMASTERFILE.Time)
AS
SumOfTime
FROM tblUserIDs INNER JOIN (tblProjectCodes INNER JOIN tblMASTERFILE ON
tblProjectCodes.[Project#] = tblMASTERFILE.[Project#]) ON tblUserIDs.[ID#]
=
tblMASTERFILE.[ID#]
WHERE (((tblMASTERFILE.Date) Between [Enter Start Date of Quarter or
Month:
(mm/dd/yyyy) ] And [Enter Finish Date: (mm/dd/yyyy) ]))
GROUP BY tblMASTERFILE.[Project#], tblProjectCodes.LayoutID,
tblProjectCodes.Description, tblUserIDs.Auditor
ORDER BY tblProjectCodes.LayoutID;
This is the second that is pulling info from this ^ that one
SELECT test2.[Project#], test2.LayoutID, test2.Description,
fConcatFld("test2","Project#","Auditor","string",[Project#]) AS Auditors,
Sum(test2.SumOfTime) AS [Sum Of Time]
FROM test2
GROUP BY test2.[Project#], test2.LayoutID, test2.Description
ORDER BY test2.LayoutID;
Allen Browne said:
There are 2 ways to approach that.
One is to build the SQL statement as a string, concatenating the literal
values in rather than using [Forms].[Form1].[Text27] in the query, e.g.:
Dim strSql As String
strSql = "SELECT ... WHERE SomeField = 99;"
The other is to supply the parameters on the querydef, e.g.:
Dim qdf As QueryDef
qdf.Parameters("[Forms].[Form1].[Text27]") = [Forms].[Form1].[Text27]
I got it to work partly. My results are based on a Query, now that Query
has a date requirement without the date requirement it works great.
but then add the date part everything else comes out out on the
second query but the concatenated field and i get an error that says
Error# 3061 Too few Parameters. Expexted 2
:
Requires code.
For details, see:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm
If someone can help me that would be great.
First problem:
I have a table basically contains time spend on various projects so
most of the entires are the same besides time and date and name.
Is there a way to list those projects and combine all the names of
the people that worked on them into one field.
