Query doesn't Group by year 1

[rudolfelizabeth]

Hi,

I have a number database with a query with three columns to group by year
Right_hits: Right(Format([Drawnumber],"0000"),2), TotalHits: Count(*) , DrawDate criteria groupby Year(2016)

The query runs without giving no info of the year 2016 I have data in the database of the year 2016.

 

[combo]

How is your query looks like? Do you need to group by years or select year 2016? Assuming you hve a field [TheYear] where you store the year, in the first case use [tt]GROUP BY TheYear[/tt], in the second [tt]WHERE TheYear=2016[/tt]. You can extract year from the date using [tt]Year[/tt] function.

combo

 

[rudolfelizabeth]

Hi combo attached query no results

 

[combo]

Year(2016)=1905. You may need Field: Year([DrawDate]), Criteria: 2016, depending on the format and contents of DrawDate.

combo

 

[rudolfelizabeth]

Hi Combo,
I've tried your solution but you cannot groupby on totalhits when groupby on totalhits you get an error
the query doesn't sum the total hits for each number
Right_hits: Right(Format([Drawnumber],"00000"),2) TotalHits: Count(*) Year([DrawDate]) drawdate
groupby expression 2016 criteria groupby
expression

 

[combo]

Can you post the sql and tell what type of data is [DrawDate]? If DrawDate is real date, you need to use Year function in "Field:" line and put 2016 in "Criteria:" line.

combo

 

[rudolfelizabeth]

Hi combo here the sql
SELECT Right(Format([Drawnumber],"00000"),2) AS Right_hits, Count(*) AS TotalHits, Numbers_data.Drawnumber, Numbers_data.DrawDate, Year([drawdate]) AS Expr1
FROM Numbers_data
GROUP BY Right(Format([Drawnumber],"00000"),2), Numbers_data.Drawnumber, Numbers_data.DrawDate
HAVING (((Year([drawdate]))=2016))
ORDER BY Right(Format([Drawnumber],"00000"),2);
Drawdate is a date field

 

[rudolfelizabeth]

problem solved