As Of Date function

[murch.alexander]

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

[murch.alexander]

Oops, not "recompile" the database. I meant "compact" the database.

[Allen Browne]

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.comwrot e in message
news:f7******** *************** ***********@f63 g2000hsf.google groups.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

[lyle fairfield]

Perhaps you should check for a valid AsOfDate with the GetAsOfDate
Function.

e.g. (air code)

Dim mAsOfDate As Date

Public Function LetAsOfDate(Opt ional vDate As Date)
On Error GoTo AsOfDateErr
If CDbl(vDate) = 0 Then
vDate = CDate(InputBox( "Enter As Of Date in format yyyy-mm-
dd", "As Of Date", Format(VBA.Date (), "yyyy-mm-dd")))
End If
mAsOfDate = vDate
AsOfDateExit:
mAsOfDate = GetAsOfDate()
Exit Function
AsOfDateErr:
MsgBox Err.Description , vbInformation, "error " & Err.Number
Resume AsOfDateExit
End Function

Public Function GetAsOfDate() As Date
If CDbl(mAsOfDate) = 0 Then LetAsOfDate
GetAsOfDate = mAsOfDate
End Function

On Oct 17, 3:39*pm, murch.alexan... @gmail.com wrote:

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