ACCESS FEED: [MS_AccessPros] Re: VBA question: my date variables are defaulting to 12/30/1899 and I can't figure out why!

Shay

I'm glad you found and posted the solution. When using dates as parameters I ALWAYS pass them as Variants. If you pass them as anything else they default to their initialized value if null.

The Variant can be tested using IsNull or IsMissing. The others won't pass those tests.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.com, Shay Holmes <shaybellaholmes@...> wrote:
>
> Hi, Crystal,
>
> I tested what value -99 comes back as within the date data type, and the
> VBA engine thinks it's 9/22/1899, which produces a similar "blank out" as
> setting the default value to 1/1/101. I did keep looking, and came across
> Allen Browne's tips at http://allenbrowne.com/ser-30.html
>
> I don't know if there's a performance hit for changing the data type to
> variant, but it does do a nice end-run around making a particular date the
> default test for a whether a date gets passed.
>
> The code to date, for anyone interested, is ...
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> Public Function WhatSY(Optional ByVal dtDate As Variant) As Date
>
> ' the purpose of this function is to return the current reporting year
> ' with the option of checking specific dates for their reporting years
> ' assumes reporting periods follow fiscal year starts
>
>
> Dim dtPassed As Date
> Dim dtWhen As Date
> Dim nY As Integer
> Dim nM As Integer
> Dim nD As Integer
> Dim dtFYStart As Date
> Dim dtRptStart As Date
> Dim nFYM As Integer
> Dim nFYD As Integer
> Dim nRM As Integer
> Dim nRD As Integer
> Dim nOffSet As Single
> Dim booRD As Boolean
>
> dtFYStart = #7/1/2000# ' don't care about the year, just set the month, day
> dtRptStart = #2/16/2000# ' don't care about the year, just set the month,
> day
> nOffSet = 1 ' difference between the calendar years from FY start and
> reporting start, s/b positive
>
> nFYM = Month(dtFYStart)
> nFYD = Day(dtFYStart)
> nRM = Month(dtRptStart)
> nRD = Day(dtRptStart)
>
> Debug.Print "The time is now: " & Format(Now(), "yyyy-mm-dd hh:mm:ss") & _
> vbCr & " The value of dtDate is: " & CStr(Nz(dtDate, "NULL"))
>
> If IsDate(dtDate) Then
> dtPassed = CDate(dtDate)
> Debug.Print "The date passed was: " & Format(dtPassed, "yyyy-mm-dd
> hh:mm:ss")
> nY = Year(dtPassed)
> nM = Month(dtPassed)
> nD = Day(dtPassed)
> Else
> Debug.Print "No useful date was passed"
> nY = Year(Date)
> nM = Month(Date)
> nD = Day(Date)
> End If
>
> Debug.Print " Year : " & nY & vbCr & " Month: " & nM & vbCr & " Day : " &
> nD
>
> dtWhen = DateSerial(nY, nM, nD)
>
> Debug.Print "The date I'm testing is: " & Format(dtWhen, "yyyy-mm-dd")
>
> ' test my date versus the reporting date,
> ' and flip my switch if the day is after the reporting period
> ' starting day
>
> If Month(dtWhen) = nRM Then
> If Day(dtWhen) >= nRD Then booRD = True
> Else
> booRD = False
> End If
>
> Debug.Print "My switch is : " & booRD
>
> If booRD Then
> If nRM < nFYM Then
> ' if the reporting period starts in the next year,
> ' then the month is less than the fiscal year start
> WhatSY = DateSerial(Year(dtWhen) - (nOffSet), nFYM, nFYD)
> Else
> ' the reporting period starts in the same calendar year
> ' as the fiscal year starts
> WhatSY = DateSerial(Year(dtWhen), nFYM, nFYD)
> End If
> Else
> If nRM < nFYM Then
> ' if the reporting period starts in the next year,
> ' then the month is less than the fiscal year start
> Select Case Month(dtWhen)
> Case 1 To nRM
> WhatSY = DateSerial(Year(dtWhen) - (nOffSet + 1), nFYM, nFYD)
> Case nFYM To 12
> WhatSY = DateSerial(Year(dtWhen) - nOffSet, nFYM, nFYD)
> Case Else
> WhatSY = DateSerial(Year(dtWhen), nFYM, nFYD)
> End Select
> Else
> ' the reporting period starts in the same calendar year
> ' as the fiscal year starts
> Select Case Month(dtWhen)
> Case 1 To nRM
> WhatSY = DateSerial(Year(dtWhen) - nOffSet, nFYM, nFYD)
> Case (nRM + 1) To 12
> WhatSY = DateSerial(Year(dtWhen), nFYM, nFYD)
> End Select
> End If
> End If
>
> End Function
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
> ~*~*~*~*~*~*~
> Shay Bella Holmes
> ~*~*~*~*~*~*~
>
>
>
> On Tue, Nov 29, 2011 at 4:02 PM, Crystal <strive4peace2008@...> wrote:
>
> > **
> >
> >
> > (Optional ByVal dtDate As Date = -99)
> >
> > then if it is missing, its value will be -99 ... doubt that will be a
> > conflict ;)
> >
> > '~~~~~~~~~~~~~~~~~~~~~~~~
> > If NOT dtDate = -99 Then
> >
> > ' date was sent ...
> >
> > If dtDate > #1./1/2000# Then
> >
> > 'date is in range
> > 'Do stuff
> > end if
> > end if
> > '~~~~~~~~~~~~~~~~~~~~~~~~
> >
> > WHERE
> > 1./1/2000 is a date that will be earlier than any dates entered
> >
> > you can also leave it as is and test for 0. Date/time is stored as a
> > number
> >
> > ? format(0,"mm-dd-yy h:nn") = 12-30-99 0:00
> >
> > Warm Regards,
> > Crystal
> >
> > *
> > (: have an awesome day :)
> > *
> >
> > ________________________________
> > From: Shay Holmes
> > Subject: [MS_AccessPros] Re: VBA question: my date variables are
> > defaulting to 12/30/1899 and I can't figure out why!
> >
> >
> > further debug statements helped me figure out that the point that's
> > misfiring is at the test for IsMissing(dtDate).
> >
> > I can reasonable assume that no one will try to pass the date #1/1/101# for
> > the reporting year, but my inner neat freak is shouting about introducing
> > an intentional failure if I were to set the default value of dtDate to
> > #1/1/101#, so is there another way to test for not getting an optional date
> > value?
> >
> > ~*~*~*~*~*~*~
> > Shay Bella Holmes
> > ~*~*~*~*~*~*~
> >
> > On Tue, Nov 29, 2011 at 2:56 PM, Shay Holmes <shaybellaholmes@...
> > >wrote:
> >
> > > I'm not sure if I have something screwy going on with my installation of
> > > Access or if I found a bug, but any help working around this would be
> > great!
> > >
> > > Running Access 2010 (v14.0.6112.5000 (32-bit)) - I don't see any Office
> > > Service Packs, but I'm not in control of that. .... and if it makes a
> > > difference, the OS is WinXP Pro SP 3 (32-bit)
> > >
> > > my debug statements show ....
> > >
> > > ? whatsy()
> > > The time is now ... 2011-11-29 14:35:12
> > > Year: 1899 Month: 12 Day: 30
> > > WHEN: 1899-12-30 00:00:00
> > > 7/1/1899
> > >
> > > the function is ....
> > >
> > > ~~~~~~~~~~~~~~~~
> > >
> > > Public Function WhatSY(Optional ByVal dtDate As Date) As Date
> > > Dim dtWhen As Date
> > > Dim nY As Integer
> > > Dim nM As Integer
> > > Dim nD As Integer
> > >
> > > Debug.Print "The time is now ... " & Format(Now(), "yyyy-mm-dd hh:mm:ss")
> > >
> > > If IsMissing(dtDate) Then ' Or IsNull(dtDate) Or Len(dtDate) < 1 Then
> > > nY = Year(Now())
> > > nM = Month(Now())
> > > nD = Day(Now())
> > > Else
> > > nY = Year(dtDate)
> > > nM = Month(dtDate)
> > > nD = Day(dtDate)
> > > End If
> > >
> > > Debug.Print "Year: " & nY & " Month: " & nM & " Day: " & nD
> > >
> > > dtWhen = DateSerial(nY, nM, nD)
> > >
> > > Debug.Print "WHEN: " & Format(dtWhen, "yyyy-mm-dd hh:mm:ss")
> > >
> > > Select Case Month(dtWhen)
> > > Case Month(dtWhen) >= 7
> > > WhatSY = DateSerial(Year(dtWhen) - 1, 7, 1)
> > > Case Month(dtWhen) < 3
> > > WhatSY = DateSerial(Year(dtWhen) - 2, 7, 1)
> > > Case Else
> > > WhatSY = DateSerial(Year(dtWhen), 7, 1)
> > > End Select
> > >
> > > End Function
> > >
> > > Note: the purpose of the function is to figure out which year is
> > currently
> > > being reported on, where the fiscal year starts July 1st, but the
> > reporting
> > > period doesn't start until after February, and ends the following
> > February.
> > > Example: fiscal year 10/11 starts reporting on 3/1/11 and ends on
> > 2/28/12.
> > > At some point, I'll probably set up a global variable for when the
> > > reporting period starts and use that instead of the hard-coded February
> > >
> > > ~*~*~*~*~*~*~
> > > Shay Bella Holmes
> > > ~*~*~*~*~*~*~
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>