How do I store only YYYY in =Date( ) text box? 2

[KerryL]

I can't get an expression to work because of the way a date is being stored in a text box.

txt box = frmRptCriteria.txtYYYY
Its control source is =Date()
It's formatted as YYYY

Even though its formatted as "YYYY" it stores a full date, 3/2/2007 instead of just 2007. This causes the following expression in a report to find zero records instead of 34:

=Sum(Abs(Year([EntryDate])=[Forms]![frmRptCriteria]![txtYYYY]))

How can I make [txtYYYY] store just the current year instead of a full date so that the expression finds records matching only YYYY?

 

[randy700]

How about this?

=Sum(Abs(Year([EntryDate])=[COLOR=red]Right([/color][Forms]![frmRptCriteria]![txtYYYY][COLOR=red],4)[/color]))

Randy

 

[KerryL]

Randy,

Wow, it worked like a charm. I would never have figured that one out.

Thank you very much!!

Hopefully I can adapt that expression for the other 5 "YTD" calculations I have to include on this report. If not, you may see me again...

Thanks again,
Kerry

 

[PHV]

A simpler way was to change the ControlSource of frmRptCriteria!txtYYYY to =Year(Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886