SQL Count stmt for YTD report field 1

[KerryL]

My summary report shows various record totals for YTD and Date Range entries. I was able to get a stmt to work for the Date Range fields, but not the YTD fields.

Report field = txtNoticesYTD
Source = tblWARNData

This report field is supposed to show a total of all records entered in the current year. To do this I wrote a stmt that counts the [RecordID] field in tblWARNData when YYYY in [EntryDate] = txtYYYY. (txtYYYY is formatted as "yyyy" with a control source of =Date().

=Count [RecordID] Where (Year([EntryDate]))=[txtYYYY]

But Access doesn't seem to like the "Where" operator and I can't figure out how else to write it. How can I count [RecordID] when YYYY in EntryDate = txtYYYY?

 

[dhookom]

I am confused. I assume you want to count the number of records where the year part of EntryDate equals the current year. If this is the case, use a control source of:
=Sum(Abs(Year([EntryDate])=Year(Date())))



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[KerryL]

Thanks Duane, that works great and is simpler than the expression I was using.

If I want to use a second variable I would add it something like this?

=Sum(Abs(Year([EntryDate])=(Year(Date())) And ([IncidType]=6)))

(I have a hard time figuring out where the parens go.)

 

[dhookom]

I have a hard time picturing where the ()s go also.
Some times it helps to work in notepad like:
[tt][blue]
=Sum(
Abs(
Year([EntryDate])=Year(Date()) And
[IncidType]=6
)
)
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]