Shay-
There's a teensy tinesy "hit" for using Variant instead of Date, but if you want
to test for IsMissing, it must be a Variant. A Variant is the only data type
that can be Null or "missing."
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Shay Holmes
Sent: Wednesday, November 30, 2011 2:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: VBA question: my date variables are defaulting
to 12/30/1899 and I can't figure out why!
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@yahoo.com> 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@gmail.com
> >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]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar