Day zero of the Access date system was December 30, 1899. If Access looks at
a variable, and treats it as a string value rather than a date, it can treat
it as day zero, and hence the problem you describe.
The solution has to do with the way you pass and return dates to/from the
function. I would imagine that the simplest approach would be pass two
values into your function:
a) the value whose age you are trying to determine;
b) the 'as-of' date.
You could make (b) optional, and have the function use today if missing.
Another factor here is that the Date in VBA cannot handle Null. (That's true
of all VBA types except variant.) It's quite common for
queries/forms/reports to pass a null value to a function. Even if the field
is required, it can happen (e.g. outer joins, or even the primary key value
at a new record.) In general, this means the function needs to accept and
return a Variant, and the query needs to massage the returned value.
So, you might declare the function like this:
Function Age(varDate As Variant, Optional varAsOf As Variant) As Variant
Then in the query, to get the age based on the DOB field as of today, you
would type an expression like this in the Field row in query design:
Age([DOB])
Or to get the age as of Jan 1 next year:
Age([DOB], #1/1/2009#)
Example of such a function:
http://allenbrowne.com/func-08.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<mu*************@gmail.comwrote in message
news:f7**********************************@f63g2000 hsf.googlegroups.com...
>I made a simple public function to set and return a date value (see
below). I have a number of queries that call up the function to get
the "As Of Date," which is typically set to today's date. Occasionally
though, I need to change the "As Of Date" to some date in the past,
and then when I run the queries, they're based on that date in the
past. This generally works fine, but, sometimes, the date gets reset
to something WAY in the past like "1/1/1900 12:00:00AM" (it's always
the same, I just can't recall the exact date).
Once that happens, the only way to get the date to reset to today's
date is to recompile the database. I checked everywhere I was setting
the date to ensure that I was using the right data type. No problems
there. Any ideas? (see code below) Thanks!
Option Compare Database
Option Explicit
Public dteAsOfDate As Date
Function SetAsOfDate(dte As Date)
dteAsOfDate = dte
End Function
Function GetAsOfDate() As Date
GetAsOfDate = dteAsOfDate
End Function
