 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
September 30th, 2005, 01:39 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
It gives syntax error (missing operator) in expression "your code".
When I click ok, it highlights the WHERE part.
Dharmesh Bhavsar
|
|
September 30th, 2005, 01:43 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Ok,
Cut and paste this one:
SELECT [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, ([Minutes]/60) AS [Total Hours], [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Date, Calendar.Month
FROM [Extrusion DT Log] INNER JOIN (Calendar INNER JOIN [Extrusion Report] ON Calendar.Date = [Extrusion Report].Date) ON [Extrusion DT Log].[Extrusion DT Id] = [Extrusion Report].DownTimeList
GROUP BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
WHERE (((Datepart("m",Calendar.Date)) = Datepart("m",Date())))
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;
Let me know.
Kevin
dartcoach
|
|
September 30th, 2005, 01:45 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
same problem.
Dharmesh Bhavsar
|
|
September 30th, 2005, 01:49 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Ok,
Change the WHERE back to HAVING
Kevin
dartcoach
|
|
September 30th, 2005, 01:51 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
IT gives an error "You tried to execute a query that does not include specified expression "Date" as a part of an aggregate function."
Dharmesh Bhavsar
|
|
September 30th, 2005, 01:58 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Dharmesh,
I don't know. It's got me baffled. I've tested the syntax and it works.
I'm not using joins, so maybe there is something that I'm missing. Can anyone else out there help??
Kevin
dartcoach
|
|
September 30th, 2005, 02:02 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Well, Thank You so much for your effort and help. I'm really bad at coding and SQL. I thought there might be a way to type in a parameter in a way where it should make it work. I know that you can have user put maybe first 3 letters of Month and it will take it as a Month name.
Dharmesh Bhavsar
|
|
September 30th, 2005, 02:07 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Dharmesh,
Sorry, without your tables, queries, forms and reports, I can't re-create it here. It looks like the report is dependent on a group of tables and queries. Without all the data, I can't figure it out from here - sorry.
Kevin
dartcoach
|
|
September 30th, 2005, 02:12 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks anyways.
Dharmesh Bhavsar
|
|
September 30th, 2005, 02:14 PM
|
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
PARAMETERS [Month] Text ( 255 );
SELECT [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, ([Minutes]/60) AS [Total Hours], [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
FROM [Extrusion DT Log] INNER JOIN (Calendar INNER JOIN [Extrusion Report] ON Calendar.Date = [Extrusion Report].Date) ON [Extrusion DT Log].[Extrusion DT Id] = [Extrusion Report].DownTimeList
GROUP BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;
Dharmesh Bhavsar
|
|
|
