I need to put criteria in a query for Hire Date. I need it to be
between the first of the year and the current date. I know i can use
the current date as Date(), however I want the first of the year to
NOT be >12/31/2009 and instead be dynamic enough to know when a new
year starts. Ideas?

On Fri, 20 Aug 2010 13:02:16 -0700 (PDT), Joe <(E-Mail Removed)> wrote:

>I need to put criteria in a query for Hire Date. I need it to be
>between the first of the year and the current date. I know i can use
>the current date as Date(), however I want the first of the year to
>NOT be >12/31/2009 and instead be dynamic enough to know when a new
>year starts. Ideas?

Try

>= DateSerial(Year(Date()), 1, 1) AND <= Date()

The DateSerial function takes three number arguments, year, month, and day, so
this expression will search from the start of this year (whenever it's run)
and today.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On Fri, 20 Aug 2010 13:02:16 -0700 (PDT), Joe <(E-Mail Removed)>
> wrote:
>
>>I need to put criteria in a query for Hire Date. I need it to be
>>between the first of the year and the current date. I know i can
>>use the current date as Date(), however I want the first of the
>>year to NOT be >12/31/2009 and instead be dynamic enough to know
>>when a new year starts. Ideas?
>
> Try
>
>>= DateSerial(Year(Date()), 1, 1) AND <= Date()
>
> The DateSerial function takes three number arguments, year, month,
> and day, so this expression will search from the start of this
> year (whenever it's run) and today.

You're assuming the field being searched has no time component (a
reasonable assumption), but many people erroneously populate date
fields (like Hire Date) with Now() instead of Date(), so to account
for that, the <= Date() might be replaced with <= Now().

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 21 Aug 2010 19:59:27 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>news:(E-Mail Removed):
>
>> On Fri, 20 Aug 2010 13:02:16 -0700 (PDT), Joe <(E-Mail Removed)>
>> wrote:
>>
>>>I need to put criteria in a query for Hire Date. I need it to be
>>>between the first of the year and the current date. I know i can
>>>use the current date as Date(), however I want the first of the
>>>year to NOT be >12/31/2009 and instead be dynamic enough to know
>>>when a new year starts. Ideas?
>>
>> Try
>>
>>>= DateSerial(Year(Date()), 1, 1) AND <= Date()
>>
>> The DateSerial function takes three number arguments, year, month,
>> and day, so this expression will search from the start of this
>> year (whenever it's run) and today.
>
>You're assuming the field being searched has no time component (a
>reasonable assumption), but many people erroneously populate date
>fields (like Hire Date) with Now() instead of Date(), so to account
>for that, the <= Date() might be replaced with <= Now().

Or even

< DateAdd("d", 1, Date())
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On 21 Aug 2010 19:59:27 GMT, "David W. Fenton"
> <(E-Mail Removed)> wrote:
>
>>John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>>news:(E-Mail Removed):
>>
>>> On Fri, 20 Aug 2010 13:02:16 -0700 (PDT), Joe
>>> <(E-Mail Removed)> wrote:
>>>
>>>>I need to put criteria in a query for Hire Date. I need it to
>>>>be between the first of the year and the current date. I know i
>>>>can use the current date as Date(), however I want the first of
>>>>the year to NOT be >12/31/2009 and instead be dynamic enough to
>>>>know when a new year starts. Ideas?
>>>
>>> Try
>>>
>>>>= DateSerial(Year(Date()), 1, 1) AND <= Date()
>>>
>>> The DateSerial function takes three number arguments, year,
>>> month, and day, so this expression will search from the start of
>>> this year (whenever it's run) and today.
>>
>>You're assuming the field being searched has no time component (a
>>reasonable assumption), but many people erroneously populate date
>>fields (like Hire Date) with Now() instead of Date(), so to
>>account for that, the <= Date() might be replaced with <= Now().
>
> Or even
>
>< DateAdd("d", 1, Date())

Or, much more simply:

<= Date() + 1

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/