Date Function Returns Wrong Date

I use the date function in MS access and just recently started returning a date 1900. However, when I break the code and type Date into the immediate window it returns the correct date.
Benjamin HelfmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
can you please show the code
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Take a look at your code. Date times are stored in Access/vba as double with the whole number portion being the dateand the decimal portion the time. The whole number represents the number of days since a base date which is 1900.

So the fact that you're getting 1900 As a date means that you're probably setting the wrong variable or control.

Jim
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, typically, a value of 0, will produce a date of 12:00:00 AM
a value of 1, will produce a date of 1/1/1899
a value of 2, will produce a date of 1/1/1900
FWIW
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
Jim and Jeffrey were busy with something else as they both know perfectly well that the real date value of numeric 0 and 1:

    CDate(0) -> 1899-12-30
    CDate(1) -> 1899-12-31

Date (and Date()) always returns the current date, so it cannot return a date of year 1900 except if your machine is set to a date of year 1900 (very unlikely), or if you do this in your code:

    Date = #1/1/1900#

which most likely will raise an "Permission denied" error.

Thus, something else is going on.

/gustav
0
Benjamin HelfmanAuthor Commented:
Here is the code.  

Sub QuickDate(ctl As Control, k As Integer)
On Error GoTo Error_Handler

'Call with on keypress event with: QuickDate Me.ActiveControl,keyascii

    If Not ctl.Locked And ctl.Enabled Then
       If InStr("+=-_tTmMhHyYrRwWkK", Chr(k)) Then     ' if a special key
           If IsNull(ctl) Then                         ' if date is already null
               ctl = Date                              '   set to today
           Else
               Select Case Chr(k)                      ' otherwise, incr/decr date:
                   Case "+", "="
                       ctl = ctl + 1                   ' incr 1 day
                   Case "-", "_"
                       ctl = ctl - 1                   ' decr 1 day
                   Case "t", "T"
                       ctl = Date                      ' set to today
                   Case "m", "M"
                       ctl = DateAdd("m", -1, ctl)     ' decr 1 month
                   Case "h", "H"
                       ctl = DateAdd("m", 1, ctl)      ' incr 1 month
                   Case "y", "Y"
                       ctl = DateAdd("yyyy", -1, ctl)  ' decr 1 year
                   Case "r", "R"
                       ctl = DateAdd("yyyy", 1, ctl)   ' incr 1 year
                   Case "w", "W"
                       ctl = DateAdd("ww", -1, ctl)    ' decr 1 week
                   Case "k", "K"
                       ctl = DateAdd("ww", 1, ctl)     ' incr 1 week
               End Select
           End If
           k = 0                                       ' cancel out key pressed
       End If
    End If
   
Exit_Procedure:
    Exit Sub
Error_Handler:
    DisplayUnexpectedError Err.Number, Err.Description
    Resume Exit_Procedure
    Resume
End Sub

I use it to press t or T for today in a date field.
0
Gustav BrockCIOCommented:
Replace this with:

            Case "t", "T"
                        Debug.Print ctl.Value
                        Debug.Print Date
                        ctl = Date                      ' set to today
                        Debug.Print ctl.Value

and tell what you see.

/gustav
0
Benjamin HelfmanAuthor Commented:
Gustav,

I replace my code with yours and got this in the immediate window:

7/18/1900
 200
7/18/1900
0
Benjamin HelfmanAuthor Commented:
But "? Date" typed into the immediate window yields today's date.
0
Gustav BrockCIOCommented:
Seems like your ctl is bound to a date field of a table from SQL Server where you (somewhere else) insert time only as year of "zero" date of SQL Server is 1900.

Why you get 200, I don't know. Do you have a control named "Date"?
What if you use Date():

                         Debug.Print ctl.Value
                         Debug.Print Date
                         ctl = Date()                      ' set to today
                         Debug.Print ctl.Value

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Benjamin HelfmanAuthor Commented:
I clicked on Date() in my code and selected definition and it turned out there was a public Enum with an element called Date which was set to 200.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.