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

Selasa, 29 November 2011

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

(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]