Error in the from clause

[Guest]

Can AnyOne see here where the error in the sql statement is
It says there is an error in the from claus

SELECT Count([Accepts 2].Queue) AS CountOfQueue,Date.Date
FROM [Accepts 2] INNER JOIN Date ON FORMAT (Date.Date,"hh") = format ( [Accepts 2].TimeOfAccept,"hh"
WHERE ((([Accepts 2].TimeOfAccept) Between (#1/1/2002#) And ((#12/30/2002#)))
Group By Date.Date;

 

[Gerald Stanley]

There are two conventions that have been broken
1. Tables and/or columns should not be named with reserved
words. Date is a reserved word and if you insist on using
it as a name of table or a column, it should be enclosed in [].
2. The ON clause should be provided in the same order as
the items being JOINed.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Can AnyOne see here where the error in the sql statement is
It says there is an error in the from clause

SELECT Count([Accepts 2].Queue) AS CountOfQueue,Date.Date
FROM [Accepts 2] INNER JOIN Date ON FORMAT

(Date.Date,"hh") = format ( [Accepts 2].TimeOfAccept,"hh")

WHERE ((([Accepts 2].TimeOfAccept) Between (#1/1/2002#) And ((#12/30/2002#))))
Group By Date.Date;
.

 

[Gerald Stanley]

Although you know that there is a gap in the results, SQL
doesn't so it would have to be told. With that in mind,
you could try creating a new table that had a single column
to hold the hours that you wanted to report on. Then you
could include this new table in your FROM clause but
remember to use an outer JOIN statement (either RIGHT or
LEFT depending upon where you placed the new table).

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Actually i was wondering 1 more thing is there a way so

that it will show all the dates in the table even if there
is no associative record i.e. instead of having

 

[Gerald Stanley]

If you had a third table (tblTime) with one column
reportHour, the SQL would look something like

SELECT Count([Accepts 2].Queue) AS CountOfQueue, Date.Date
FROM ([Accepts 2] INNER JOIN [Date] ON
FORMAT(Date.Date,"hh")=format([Accepts
2].TimeOfAccept,"hh")) RIGHT JOIN tblTime ON
FORMAT(Date.Date,"hh") = FORMAT(tblTime.reportHour, "hh")
WHERE ((([Accepts 2].TimeOfAccept) Between (#1/1/2002#) And
((#12/30/2002#))))
GROUP BY Date.Date;

The theory is that tblTime has a row for each hour that you
wish to report on. But this is untested so you will have
to play about with it.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
SELECT Count([Accepts 2].Queue) AS CountOfQueue, Date.Date
FROM [Accepts 2] RIGHT OUTER JOIN [Date] ON

FORMAT(Date.Date,"hh")=format([Accepts 2].TimeOfAccept,"hh")

WHERE ((([Accepts 2].TimeOfAccept) Between (#1/1/2002#) And ((#12/30/2002#))))
GROUP BY Date.Date;


This is what i put in but it has the same result ie it skips the zeros
i have the single column table full of times from o to 23 already i.e. the date table
.

 

[Guest]

I tried to programme the sql statement into VB but it says there is an error with the statement ie there is et
The dates are supplied by the combo bo

SELECT Count([Accepts 2].Queue),[Date2].Date
FROM [Accepts 2] INNER JOIN [Date2] ON FORMAT(Date2.Date,'hh')=format([Accepts 2].TimeOfAccept,'hh')
WHERE ((([Accepts 2].TimeOfAccept) Between #2002-04-05 15:00:01# And #2002-12-01 18:00:01#))
GROUP BY [Date2].Date

Anyone have any ideas

 

[Gerald Stanley]

The likeliest explanation is that the dates are not in the
correct format, which is American style mm/dd/yyyy

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I tried to programme the sql statement into VB but it says

there is an error with the statement ie there is etc

The dates are supplied by the combo box

SELECT Count([Accepts 2].Queue),[Date2].Date
FROM [Accepts 2] INNER JOIN [Date2] ON

FORMAT(Date2.Date,'hh')=format([Accepts 2].TimeOfAccept,'hh')

WHERE ((([Accepts 2].TimeOfAccept) Between #2002-04-05

15:00:01# And #2002-12-01 18:00:01#)))

GROUP BY [Date2].Date;

Anyone have any ideas
.