Date format and expression

[cenibyte]

I am trying to have the date display by year within a certain date range and display all the data that is required by year. I have built my query and the date is formatted to display the year, program, and amount of entries for each program... But it displays all the entries for the year. Please help.

Field: Date In By Year: Format$([Nominal Rolls].[Date In],'yyyy')

Field: Program
Table: Nominal Roll

Field: Count of Nominal Rolls: Count(*)

[pbaldy]

My guess is you have Group By in the column with the criteria. Change that to Where. If that isn't it, post the full SQL of the query.

[cenibyte]

If I change it to where it removes the criteria and groups all the totals together. How do I get the SQL?

[cenibyte]

-------------------------------------------------------------
| Date In By Year | Program | Count Of Nominal |
| | | Rolls |
-------------------------------------------------------------
| 2015 | AVOTP | 1 |
| | | |
-------------------------------------------------------------
| 2016 | AVOTP | 21 |
| | | |
-------------------------------------------------------------
| 2016 | CBRN OP | 1 |
| | | |
-------------------------------------------------------------
| 2016 | SF OP | 19 |
| | | |
-------------------------------------------------------------
| 2016 | SCP | 1 |
| | | |
-------------------------------------------------------------
| 2016 | SNIPER | 23 |
| | | |
-------------------------------------------------------------
| 2016 | COT | 8 |
| | | |
-------------------------------------------------------------
| 2016 | CT | 3 |
| | | |
-------------------------------------------------------------
| 2016 | FE | 1 |
| | | |
-------------------------------------------------------------
| 2016 | RES OT | 2 |
| | | |
-------------------------------------------------------------
| 2016 | FA | 1 |
| | | |
-------------------------------------------------------------
| 2016 | IEDD | 1 |
| | | |
-------------------------------------------------------------
| 2016 | CMOT | 1 |
| | | |
-------------------------------------------------------------

[JoeM]

How do I get the SQL?

If you have done this in Access, just change your query to SQL View and copy and paste the code here.

[pbaldy]

With the query in design view, on the ribbon click on the dropdown by the view icon and choose SQL.

[cenibyte]

With the query in design view, on the ribbon click on the dropdown by the view icon and choose SQL.

SELECT DISTINCTROW Format$([Nominal Rolls].[Date In],'yyyy') AS [Date In By Year], [Nominal Rolls].Program, Count(*) AS [Count Of Nominal Rolls]
FROM [Nominal Rolls]
GROUP BY Format$([Nominal Rolls].[Date In],'yyyy'), [Nominal Rolls].Program
HAVING (((Format$([Nominal Rolls].[Date In],'yyyy'))>#4/1/2016# And (Format$([Nominal Rolls].[Date In],'yyyy'))<#3/31/2017#));

[pbaldy]

Try

SELECT Format$([Nominal Rolls].[Date In],'yyyy') AS [Date In By Year], [Nominal Rolls].Program, Count(*) AS [Count Of Nominal Rolls]
FROM [Nominal Rolls]
WHERE [Nominal Rolls].[Date In]>#4/1/2016# And [Nominal Rolls].[Date In]<#3/31/2017#
GROUP BY Format$([Nominal Rolls].[Date In],'yyyy'), [Nominal Rolls].Program

[JoeM]

Your "Format$" function are not returning dates, they are returning years (in Text format). So you still want to apply criteria to the underlying dates.

Try this:

SELECT DISTINCTROW Format$([Nominal Rolls].[Date In],'yyyy') AS [Date In By Year], [Nominal Rolls].Program, Count(*) AS [Count Of Nominal Rolls]
FROM [Nominal Rolls]
WHERE (([Nominal Rolls].[Date In]>#4/1/2016#) And ([Nominal Rolls].[Date In]<#3/31/2017#))
GROUP BY Format$([Nominal Rolls].[Date In],'yyyy'), [Nominal Rolls].Program;

Edit: Yeah, what Paul said!