Solved

# Access 2007 Simple Custom Date Function Returning Very Weird Results

Posted on 2010-01-08
227 Views
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!

0
Question by:g8tor23

LVL 119

Accepted Solution

test

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

LVL 2

Author Comment

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

## Featured Post

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — 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…