Access 2007 Simple Custom Date Function Returning Very Weird Results

Posted on 2010-01-08
Last Modified: 2013-11-27
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


>=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


Question by:g8tor23
    LVL 119

    Accepted Solution


    Function SimDate() as date
    SimDate = #1/8/2010#
    End Function
    LVL 2

    Author Comment

    Thanks for the quick response...guess I needed to declare the data type...doh!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now