Dates are killing me!

[Voodoo223]

Thanks for the help. I'm getting through this db.
I am trying to get user input of start date and End date for the following:

SELECT [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code] AS [Q5 TCs], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date]
FROM [ASC Cust Count_Crosstab] INNER JOIN [Oracle Account TC] ON [ASC Cust Count_Crosstab].Node = [Oracle Account TC].Node
GROUP BY [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date], [ASC Cust Count_Crosstab].ASC, [Oracle Account TC].[Q Code]
HAVING ((([Oracle Account TC].[Q Code])="5") AND (([Oracle Account TC].[Check In Date]) Between [Start Date] And [End Date]) AND (([ASC Cust Count_Crosstab].ASC)="uh"));


But I get a reply back "The Microsoft Jet database does not recognize '[Start Date]' as a valid field name or expression.
Could someone tell me what I'm doing wrong. They are date fields. Thanks.

 

[Jon K]

Since your query involves a crosstab query, you have to declare the data type of the parameters.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code] AS [Q5 TCs], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date]
FROM [ASC Cust Count_Crosstab] INNER JOIN [Oracle Account TC] ON [ASC Cust Count_Crosstab].Node = [Oracle Account TC].Node
GROUP BY [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date], [ASC Cust Count_Crosstab].ASC, [Oracle Account TC].[Q Code]
HAVING ((([Oracle Account TC].[Q Code])="5") AND (([Oracle Account TC].[Check In Date]) Between [Start Date] And [End Date]) AND (([ASC Cust Count_Crosstab].ASC)="uh"));


Not sure why you have to use a totals query though. In your query, you haven't used any of the aggregate functions of Sum(), Count() etc.
.

 

[Voodoo223]

This Query is pulling Info from the crosstab and a table. The crosstab is used because the table I have to import has the products in the records so each node is listed 4 times per date. Is there a better way of doing this?

 

[Jon K]

Since you haven't used any aggregate functions, you don't need a totals query. Try deleting the GROUP BY clause and changing HAVING to WHERE, which should return the same results.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [ASC Cust Count_Crosstab].[Node], [Oracle Account TC].[Q Code] AS [Q5 TCs], [ASC Cust Count_Crosstab].[Video], [Oracle Account TC].[Check In Date]
FROM [ASC Cust Count_Crosstab] INNER JOIN [Oracle Account TC] ON [ASC Cust Count_Crosstab].[Node]=[Oracle Account TC].[Node]
WHERE ((([Oracle Account TC].[Q Code])="5") And (([Oracle Account TC].[Check In Date]) Between [Start Date] And [End Date]) And (([ASC Cust Count_Crosstab].[ASC])="uh"));


If duplicate records are returned, you can use the DISTINCT key word to return unique results:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCT [ASC Cust Count_Crosstab].Node, ...........
.

 

[Voodoo223]

Thank you, the DISTINCT key wasn't nessessary. The query is pulling the info as expected.