If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
**Please Help** Select Details.
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. |
#2
|
|||
|
|||
**Please Help** Select Details.
Insert the following as first line in the SQL for the crosstab query --
PARAMETERS [Forms]![frmReportExportCriteria]![txtStartDate] TEXT 255; -- KARL DEWEY Build a little - Test a little "Access To Access" wrote: 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. |
#3
|
|||
|
|||
**Please Help** Select Details.
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" wrote: 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. |
#4
|
|||
|
|||
**Please Help** Select Details.
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" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
**Please Help** Select Details.
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" wrote in message ... 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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|