**Please Help** Select Details.

A

Access To Access

I have set up a reporting DB which will export data from queries into excel
on the users c:/ drive.

To do this i created a simple user from with Start Date, End Date and Client
text boxes.

In the queries i used the [Forms]![frmReportExportCriteria]![txtStartDate]
in the criteria part of the query. So when they run the criteria it
automatically looks these values up, produces the data and exports.

But i have a crosstab which is based on one of these queries which will not
run with this setup. I have to actually save the value i want to input into
start date ie using #19/04/2007# which means the user would need to go into
the queries to both input and then run the report.

Why is this happening, what am i missing. The error i get is "the microsoft
Jet Database engine does not regognise
'Forms]![frmReportExportCriteria]![txtStartDate]as a valid fieldname or
expression.

I really need to get the criteria reading from the form not typed in the
query back-end.
 
K

KARL DEWEY

Insert the following as first line in the SQL for the crosstab query --
PARAMETERS [Forms]![frmReportExportCriteria]![txtStartDate] TEXT 255;
 
A

Access To Access

Oh Here is my first query which runs fine.

SELECT CDate(Left([CallDateTime],10)) AS [Date], dbo_CRC.CRC,
Count(dbo_History.PhoneNum) AS Connects
FROM ((dbo_CRCGroup INNER JOIN dbo_CRCGroupProject ON dbo_CRCGroup.GroupID =
dbo_CRCGroupProject.GroupID) INNER JOIN (dbo_History INNER JOIN dbo_CRC ON
dbo_History.CRC = dbo_CRC.CRC) ON dbo_CRCGroupProject.ProjectID =
dbo_History.ProjectID) INNER JOIN dbo_Agent ON dbo_History.AgentID =
dbo_Agent.AgentID
GROUP BY CDate(Left([CallDateTime],10)), dbo_CRC.CRC, dbo_CRCGroup.GroupName
HAVING
(((CDate(Left([CallDateTime],10)))=[Forms]![frmReportExportCriteria]![txtStartDate]) AND ((dbo_CRCGroup.GroupName)="BainsErnst"))
ORDER BY CDate(Left([CallDateTime],10));


and here is my crosstab query:

TRANSFORM CDbl(Nz(Sum(qryCRCLookupPart1.Connects),0)) AS SumOfConnects
SELECT qryCRCIdentify.CRC, Sum(qryCRCLookupPart1.Connects) AS [Total Of
Connects]
FROM qryCRCIdentify LEFT JOIN qryCRCLookupPart1 ON
qryCRCIdentify.CRC=qryCRCLookupPart1.CRC
GROUP BY qryCRCIdentify.CRC
PIVOT qryCRCLookupPart1.Date;
 
A

Access To Access

Well the " PARAMETERS [Forms]![frmReportExportCriteria]![txtStartDate] TEXT
255;" had an impact but now i get an error message which reads "syntax error
in parameters clause"

Access To Access said:
Oh Here is my first query which runs fine.

SELECT CDate(Left([CallDateTime],10)) AS [Date], dbo_CRC.CRC,
Count(dbo_History.PhoneNum) AS Connects
FROM ((dbo_CRCGroup INNER JOIN dbo_CRCGroupProject ON dbo_CRCGroup.GroupID =
dbo_CRCGroupProject.GroupID) INNER JOIN (dbo_History INNER JOIN dbo_CRC ON
dbo_History.CRC = dbo_CRC.CRC) ON dbo_CRCGroupProject.ProjectID =
dbo_History.ProjectID) INNER JOIN dbo_Agent ON dbo_History.AgentID =
dbo_Agent.AgentID
GROUP BY CDate(Left([CallDateTime],10)), dbo_CRC.CRC, dbo_CRCGroup.GroupName
HAVING
(((CDate(Left([CallDateTime],10)))=[Forms]![frmReportExportCriteria]![txtStartDate]) AND ((dbo_CRCGroup.GroupName)="BainsErnst"))
ORDER BY CDate(Left([CallDateTime],10));


and here is my crosstab query:

TRANSFORM CDbl(Nz(Sum(qryCRCLookupPart1.Connects),0)) AS SumOfConnects
SELECT qryCRCIdentify.CRC, Sum(qryCRCLookupPart1.Connects) AS [Total Of
Connects]
FROM qryCRCIdentify LEFT JOIN qryCRCLookupPart1 ON
qryCRCIdentify.CRC=qryCRCLookupPart1.CRC
GROUP BY qryCRCIdentify.CRC
PIVOT qryCRCLookupPart1.Date;

Access To Access said:
I have set up a reporting DB which will export data from queries into excel
on the users c:/ drive.

To do this i created a simple user from with Start Date, End Date and Client
text boxes.

In the queries i used the [Forms]![frmReportExportCriteria]![txtStartDate]
in the criteria part of the query. So when they run the criteria it
automatically looks these values up, produces the data and exports.

But i have a crosstab which is based on one of these queries which will not
run with this setup. I have to actually save the value i want to input into
start date ie using #19/04/2007# which means the user would need to go into
the queries to both input and then run the report.

Why is this happening, what am i missing. The error i get is "the microsoft
Jet Database engine does not regognise
'Forms]![frmReportExportCriteria]![txtStartDate]as a valid fieldname or
expression.

I really need to get the criteria reading from the form not typed in the
query back-end.
Click to expand...
Click to expand...
 
J

John Spencer

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

I suspect that you need to change the type of the parameter to DateTime
instead of Text.

Also check the parameter declaration and make sure Access has NOT added an
extra set of [] brackets around the entire thing.
Parameters [Forms]![frmReportExportCriteria]![txtStartDate] DateTime;

NOT
Parameters [[Forms]![frmReportExportCriteria]![txtStartDate]] DateTime;

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

Access To Access said:
Well the " PARAMETERS [Forms]![frmReportExportCriteria]![txtStartDate]
TEXT
255;" had an impact but now i get an error message which reads "syntax
error
in parameters clause"

Access To Access said:
Oh Here is my first query which runs fine.

SELECT CDate(Left([CallDateTime],10)) AS [Date], dbo_CRC.CRC,
Count(dbo_History.PhoneNum) AS Connects
FROM ((dbo_CRCGroup INNER JOIN dbo_CRCGroupProject ON
dbo_CRCGroup.GroupID =
dbo_CRCGroupProject.GroupID) INNER JOIN (dbo_History INNER JOIN dbo_CRC
ON
dbo_History.CRC = dbo_CRC.CRC) ON dbo_CRCGroupProject.ProjectID =
dbo_History.ProjectID) INNER JOIN dbo_Agent ON dbo_History.AgentID =
dbo_Agent.AgentID
GROUP BY CDate(Left([CallDateTime],10)), dbo_CRC.CRC,
dbo_CRCGroup.GroupName
HAVING
(((CDate(Left([CallDateTime],10)))=[Forms]![frmReportExportCriteria]![txtStartDate])
AND ((dbo_CRCGroup.GroupName)="BainsErnst"))
ORDER BY CDate(Left([CallDateTime],10));


and here is my crosstab query:

TRANSFORM CDbl(Nz(Sum(qryCRCLookupPart1.Connects),0)) AS SumOfConnects
SELECT qryCRCIdentify.CRC, Sum(qryCRCLookupPart1.Connects) AS [Total Of
Connects]
FROM qryCRCIdentify LEFT JOIN qryCRCLookupPart1 ON
qryCRCIdentify.CRC=qryCRCLookupPart1.CRC
GROUP BY qryCRCIdentify.CRC
PIVOT qryCRCLookupPart1.Date;

Access To Access said:
I have set up a reporting DB which will export data from queries into
excel
on the users c:/ drive.

To do this i created a simple user from with Start Date, End Date and
Client
text boxes.

In the queries i used the
[Forms]![frmReportExportCriteria]![txtStartDate]
in the criteria part of the query. So when they run the criteria it
automatically looks these values up, produces the data and exports.

But i have a crosstab which is based on one of these queries which will
not
run with this setup. I have to actually save the value i want to input
into
start date ie using #19/04/2007# which means the user would need to go
into
the queries to both input and then run the report.

Why is this happening, what am i missing. The error i get is "the
microsoft
Jet Database engine does not regognise
'Forms]![frmReportExportCriteria]![txtStartDate]as a valid fieldname or
expression.

I really need to get the criteria reading from the form not typed in
the
query back-end.
Click to expand...
Click to expand...
Click to expand...
 
Top