Select year from a query

G

Guest

I have the following SQL for my query but I want to have the user be able to
select the year only for this query. How would I write this into my SQL
statement?

SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
 
G

Guest

Try this --
SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
WHERE (((Year([DateCreated]))=[Enter year - 2004]))
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
 
G

Guest

Hi,
I tried it but I'm getting an error message that says:

You tried to execute a query that does not include the specified expression
'Format$([tblNCMR].[DateCreated],'mmmm yyyy')' as part of an aggregate
function.



KARL DEWEY said:
Try this --
SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
WHERE (((Year([DateCreated]))=[Enter year - 2004]))
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);


Secret Squirrel said:
I have the following SQL for my query but I want to have the user be able to
select the year only for this query. How would I write this into my SQL
statement?

SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
Click to expand...
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

Interesting; when I copied and pasted - and doctored - the SQL, I saw the
same error, but on the line that said

Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1

which was labeled 'GROUP BY' but, since it was not part of the SELECT (read:
display) clause, I would have gotten that error message on that line. Try
copying and pasting that line into the SQL just before the FROM clause (don't
forget to put a comma after the words [Code Description]), and see if that
clears the error message.

Sam

Secret said:
Hi,
I tried it but I'm getting an error message that says:

You tried to execute a query that does not include the specified expression
'Format$([tblNCMR].[DateCreated],'mmmm yyyy')' as part of an aggregate
function.
Try this --
SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Click to expand...
[quoted text clipped - 23 lines]
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Do not know why - it was that way when you sent it and it works for me.

Two thoughts come to mind -- try changing the single quotes to double quotes
and check to see if a hard return crept in from the posting and pasting.

Secret Squirrel said:
Hi,
I tried it but I'm getting an error message that says:

You tried to execute a query that does not include the specified expression
'Format$([tblNCMR].[DateCreated],'mmmm yyyy')' as part of an aggregate
function.



KARL DEWEY said:
Try this --
SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
WHERE (((Year([DateCreated]))=[Enter year - 2004]))
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);


Secret Squirrel said:
I have the following SQL for my query but I want to have the user be able to
select the year only for this query. How would I write this into my SQL
statement?

SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It works now. There must have been a hard return in there. Thank you very
much for your help! I appreciate it!

KARL DEWEY said:
Do not know why - it was that way when you sent it and it works for me.

Two thoughts come to mind -- try changing the single quotes to double quotes
and check to see if a hard return crept in from the posting and pasting.

Secret Squirrel said:
Hi,
I tried it but I'm getting an error message that says:

You tried to execute a query that does not include the specified expression
'Format$([tblNCMR].[DateCreated],'mmmm yyyy')' as part of an aggregate
function.



KARL DEWEY said:
Try this --
SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
WHERE (((Year([DateCreated]))=[Enter year - 2004]))
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);


:

I have the following SQL for my query but I want to have the user be able to
select the year only for this query. How would I write this into my SQL
statement?

SELECT DISTINCTROW tblNCMR.DiscrepancyCode,
Format$([tblNCMR].[DateCreated],'mmmm yyyy') AS [DateCreated By Month],
Sum(tblNCMR.TotalCost) AS [Sum Of TotalCost], Count(*) AS [Count Of tblNCMR],
Month([DateCreated]) AS MonthNum, tblDiscrepancyCodes.[Code Description]
FROM tblDiscrepancyCodes INNER JOIN tblNCMR ON tblDiscrepancyCodes.Code =
tblNCMR.DiscrepancyCode
GROUP BY tblNCMR.DiscrepancyCode, Format$([tblNCMR].[DateCreated],'mmmm
yyyy'), Month([DateCreated]), tblDiscrepancyCodes.[Code Description],
Year([tblNCMR].[DateCreated])*12+DatePart('m',[tblNCMR].[DateCreated])-1
ORDER BY Month([DateCreated]);
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A Weekly Count 3
Count YTD workdays 2
Query Question 5
Count Query 3
Sorting by Month and Year in Query 2
Nulls in a crostab query 2
Cross-Tab Query 2
Cross tab - complex IIF 2

Top