I am having a puzzling problem with an Access SQL query, and I wondered if anyone had any ideas.
My original query (which executes fine from both ColdFusion and the machine running the database) was:
SELECT Tracking.ViewDate, Firm_IPs.Firm, Articles.Title, Count(Articles.Title) AS CountOfTitle
FROM (Articles INNER JOIN Tracking ON Articles.ID = Tracking.ArticleNo) INNER JOIN Firm_IPs ON Tracking.ClientIP = Firm_IPs.IP
GROUP BY Tracking.ViewDate, Firm_IPs.Firm, Articles.Title
HAVING (((Tracking.ViewDate) Between #SDate# And #EDate#))
ORDER BY Tracking.ViewDate DESC , Firm_IPs.Firm, Count(Articles.Title) DESC;
However, when I attempt to add a filter to the Firms_IP.Firms column as follows, I get the ODBC error message below:
SELECT Tracking.ViewDate, Firm_IPs.Firm, Articles.Title, Count(Articles.Title) AS CountOfTitle
FROM (Articles INNER JOIN Tracking ON Articles.ID = Tracking.ArticleNo) INNER JOIN Firm_IPs ON Tracking.ClientIP = Firm_IPs.IP
GROUP BY Tracking.ViewDate, Firm_IPs.Firm, Articles.Title
HAVING (((Tracking.ViewDate) Between #SDate# And #EDate#) AND ((Firm_IPs.Firm) Like "Aker*"
)
ORDER BY Tracking.ViewDate DESC , Firm_IPs.Firm, Count(Articles.Title) DESC;
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (17:1) to (19:18).
Any ideas? I don't understand how I could be refernecing a field that does not exist when the original (which functioned perfectly) references the same field, although not via a HAVING.
My original query (which executes fine from both ColdFusion and the machine running the database) was:
SELECT Tracking.ViewDate, Firm_IPs.Firm, Articles.Title, Count(Articles.Title) AS CountOfTitle
FROM (Articles INNER JOIN Tracking ON Articles.ID = Tracking.ArticleNo) INNER JOIN Firm_IPs ON Tracking.ClientIP = Firm_IPs.IP
GROUP BY Tracking.ViewDate, Firm_IPs.Firm, Articles.Title
HAVING (((Tracking.ViewDate) Between #SDate# And #EDate#))
ORDER BY Tracking.ViewDate DESC , Firm_IPs.Firm, Count(Articles.Title) DESC;
However, when I attempt to add a filter to the Firms_IP.Firms column as follows, I get the ODBC error message below:
SELECT Tracking.ViewDate, Firm_IPs.Firm, Articles.Title, Count(Articles.Title) AS CountOfTitle
FROM (Articles INNER JOIN Tracking ON Articles.ID = Tracking.ArticleNo) INNER JOIN Firm_IPs ON Tracking.ClientIP = Firm_IPs.IP
GROUP BY Tracking.ViewDate, Firm_IPs.Firm, Articles.Title
HAVING (((Tracking.ViewDate) Between #SDate# And #EDate#) AND ((Firm_IPs.Firm) Like "Aker*"
)ORDER BY Tracking.ViewDate DESC , Firm_IPs.Firm, Count(Articles.Title) DESC;
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (17:1) to (19:18).
Any ideas? I don't understand how I could be refernecing a field that does not exist when the original (which functioned perfectly) references the same field, although not via a HAVING.
