Access 2007 Simple Custom Date Function Returning Very Weird Results

Hello all,

I am working on a simple custom function (really simple) so I can test some different formulas to extract data based on dynamic date ranges. I have stuff that always fails in January because I haven't properly accounted for the change in years, so this year I'm trying to fix it once and for all.

I use a combination of Date() and DateSerial for most of the stuff I do, but I want to "simulate" "today" as some other date so I can test my formulas. So I created this simple function:


Function SimDate()
SimDate = #1/8/2010#
End Function

so that I could enter any date I wanted and see if the other formulas I've developed are working. But I get some results that I cannot explain:


[ First, assume that today is january 8th, 2010. Also, the date field being queried is mm/dd/yyy]


In this example, I am creating a formula to extract the current YTD up to 4 days ago. If today is 1/8/10, then I would expect it to return data from 1/1/10 - 1/4/10.


if I use this:

>=DateSerial(Year(Date()-4),1,1) And <=Date()-4

OR THIS:

>=DateSerial(Year(#1/8/2010#-4),1,1) And <=#1/8/2010#-4

it CORRECTLY returns 1/1-1/4 (assuming today is 1/8)


However, if I use the custom function:

Function SimDate()
SimDate = #1/8/2010#
End Function

and use it this way in the query:

>=DateSerial(Year(SimDate()-4),1,1) And <=SimDate()-4

It INCORRECTLY returns data between 1/1 - 1/6 (assuming today is 1/8)


Thanks in advance to anyone who can help me determine why I get different results using the function than if I hardcoded the same date value. In other words, why this:

>=DateSerial(Year(#1/8/2010#-4),1,1) And <=#1/8/2010#-4

returns different results than this:

Function SimDate()
SimDate = #1/8/2010#
End Function

and use it this way in the query:

>=DateSerial(Year(SimDate()-4),1,1) And <=SimDate()-4


Cheers!