Comparing numeric object on form to ddmmyyyy field in table

[KerryL]

How can I use a 4-digit numeric field to filter records whose date field is formatted as a short date (mm/dd/yyyy)?

On frmRptCriteria there's a data object called txtYear. Users enter a 4-digit year into this field to specify which fiscal year the report is for. If a user enters "2007" into frmRptCriteria.txtYear, I need the report object to use that entry to produce this report data:

1. Number of records entered YTD (2007)
2. Number of records entered in previous year

The report source = tblWARNData;
the corresponding date field in the table = EntryDate.

So for #1 above I added this statement to the report object's control source:

=Sum(Abs([EntryDate]=[Forms]![frmRptCriteria]![txtYear]))

But I know this won't work because [EntryDate] and [txtYear] are not formatted the same.

How can I compare "2007" in [txtYear] to just the YYYY in [EntryDate] so that the stmt counts all records with an EntryDate of 1/1/2007 - 12/31/2007?


For #2 do I need to modify it something like this:

=Sum(Abs([EntryDate]=([Forms]![frmRptCriteria]![txtYear]-1)))


Thanks in advance!

 

[traingamer]

Try Year([entryDate])

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

Gamer,
I tried your suggestion within this statement:

=Sum(Abs(YEAR[EntryDate]=[Forms]![frmRptCriteria]![txtYear]))

However, I get a syntax (operand without an operator) error.

 

[traingamer]

Abs(Year(Date())="2007")

works in a query as does

SELECT Sum(Abs(Year(Date())="2007")) AS zz
FROM tblTest;

=Sum(Abs(Year(Date())="2007"))

works just fine in a report footer as well

duhh after typing all this - you just need parens

=Sum(Abs(YEAR[COLOR=red]([/color][EntryDate][COLOR=red])[/color]=[Forms]![frmRptCriteria]![txtYear]))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[KerryL]

Drat, I thought I tried the parens there. Guess not. Thanks Greg!


Of course, now I'm having syntax problems with another statement. The error message states I have too many right parens, but I have 4L & 4R. So I assume something is in the wrong place, but so far no luck. Can you take a look?

If(Year([EntryDate])=[Forms]![frmRptCriteria]![txtYear]) Then Sum(Abs([IncidType])="3")

Again I need to first find only records that match the year in frmRptCriteria.txtYear. Then from those that match I'm trying to count those where IncidType = 3.