Date and Time Functions not using current info

[Kevin320]

Has anyone encountered a situation where the Date and Time functions in VBA are not using current data? I have VBA code that uses Time to determine certain actions. A new associate took a copy of that code and started tinkering with it to complete a project I assigned. Now, his copy of the code returns old data for the Date and Time functions. It returns 5/27/2014 for Date and 7:15:42 AM for Time. The Now function works properly. Additionally, running the Date and Time functions in queries works fine. I've compacted and repaired the Access database, I've checked the references, and I've checked to see if he assigned values to variables named Date or Time, but I don't see anything wrong. What am I missing? Does anyone have any suggestions?

Thanks

 

[GinaWhipp]

Nope, so two choices...

1. Old data (or is it attached to the same backend)
2. Check their *tinkering* as they changed something.

 

[Kevin320]

Hi Gina,

Thanks for the quick response. I don't think it's old data because the Now function shows current date/time and Date and Time functions work correctly in queries. I figured he changed something while "tinkering" but I can't see anything that would cause Date and Time to produce incorrect data. He's experienced in Excel VBA, but is very new to Access VBA. I can pull the usable code from his copy and add it to another copy of my existing code, but this is bothering me, and I'm sure it's probably something simple. I just can't figure it out.

 

[GinaWhipp]

Now() will always return the current date/time even in a database with data from last year. The same with Date() function. You need to look in the tables.

 

[Kevin320]

I'm confused. What tables would Time() and Date be pulling from? I thought those functions pulled the data from the current system date/time, not tables. When I create a brand new Access database, with no tables at all, I can write simple VBA code to pull current date, time, and now information.

I'm at a loss here. I've had him scrap the "corrupted" database, create another copy of the original database and copy the VBA into it. That version works fine, so we'll just move on with it, but it still bothers me that the system date/time was not correct for those two functions in VBA.

 

[vbaInet]

Perhaps you should check what Date() and Time() functions display in a text box. And do the same in VBA via a msgbox. For the latter, using a button put code in the click event to display the date and time like this:

Msgbox Date() & vbnewLine & Time()

Tell us what you get for both test cases.

 

[Kevin320]

Perhaps you should check what Date() and Time() functions display in a text box. And do the same in VBA via a msgbox. For the latter, using a button put code in the click event to display the date and time like this:

Msgbox Date() & vbnewLine & Time()

Tell us what you get for both test cases.

Hi vbaInet,

I hope you've hit on something, and have an idea about what is going on. I've been looking at this using queries, msgbox, and by stepping through the code. When I look at Date() and Time() via msgbox, 5/27/2014 and 7:16:35 AM are displayed, and Now() displays the current system date/time. When I step through the code (if - then statements that are looking at Date() or Time()), the data listed above is being used. With queries and text boxes, however, the appropriate system date/time is displayed. Does this make sense to you?

Thanks,

Kevin

 

[vbaInet]

So to clarify you get the wrong date and time when you test it via a Msgbox() and in a query? But you get the correct Date/Time when you use Now()?

If this is the case, how did you test Now()?

 

[ino_mart]

Did you perform a search-command through your whole project on the words date and time? In Access 2013 it is possible to tweak the system command Time. This does not work for Date in Access 2013 (but it might be in former versions).

Open a new access database and press ALT+F11 to go to the VBA-code
In the direct-pane, type
?time
This should result in the correct time

Add now a module in this new project and add next code:

 Option Compare Database
Public Const time = #12:45:00 PM#

In the direct-pane re-execute ?time
This will result in 12:45:00 PM instead of the correct time.

 

[Kevin320]

So to clarify you get the wrong date and time when you test it via a Msgbox() and in a query? But you get the correct Date/Time when you use Now()?

If this is the case, how did you test Now()?

Yes and no. Here are the test cases and my results:

MsgBox

I create a Form, add a button with an On Click Event Procedure. In the Private Sub called by the Event Procedure, I add the following code:

MsgBox "Now: " & Now() & vbCrLf & "Date: " & Date & vbCrLf & "Time: " & Time()

The result when I run it is:

Now: 7/3/2014 8:37:03 AM
Date: 5/27/2014
Time: 7:16:35 AM

Therefore, MsgBox produces the correct date and time with Now(), but the incorrect information with Date() and Time().

Query

In a select query, I write the following code:

SELECT Now() AS N, Date() AS D, Time() AS T;

The result from running the query is:

Query1NDT7/3/2014 8:43:45 AM7/3/20148:43:45 AM

Therefore, all three functions (i.e., Now(), Date(), and Time()) work properly in queries.

Text Box

I create a Form, add three text boxes, one for each function. The text boxes have the following:

=Now()
=Date()
=Time()

The results are all current date/time information, so the functions work properly in text boxes.



The bottom line is that the only place Date() and Time() are incorrect is in VBA code. Even when I step through the code and look at the values assigned during If/Then statements, Date() and Time() are incorrect in VBA.

Thanks for your thoughts and help.

Kevin

 

[vbaInet]

I think there's a problem with VBA's Date/Time library.

Create a brand new database and run the same tests.

 

[RainLover]

Time is a Text Format. Why would you use that.

Do you have a problem if you stick to Date and Now. If the answer is no then why use Time.

Just a passing thought as I really don't understand what you are trying to do here.

 

[vbaInet]

Time is a Text Format. Why would you use that.

Do you have a problem if you stick to Date and Now. If the answer is no then why use Time.

Just a passing thought as I really don't understand what you are trying to do here.

The problem here is the Date() function isn't returning the correct date but Now() does. If you look at the test cases in the OP's last post you'll understand what's happening.

 

[Kevin320]

Did you perform a search-command through your whole project on the words date and time? In Access 2013 it is possible to tweak the system command Time. This does not work for Date in Access 2013 (but it might be in former versions).

Open a new access database and press ALT+F11 to go to the VBA-code
In the direct-pane, type
?time
This should result in the correct time

Add now a module in this new project and add next code:

 Option Compare Database
Public Const time = #12:45:00 PM#

In the direct-pane re-execute ?time
This will result in 12:45:00 PM instead of the correct time.

I have done a search of the whole project for date, but not time yet. I don't see anything out of the norm with date. Unfortunately, I don't know what you mean by "direct-pane", but I don't see anyplace where he has set date or time as anything else.

Following-up on something GinaWhipp suggested, I started looking at values in tables within the database. I came across one with the offensive date and time information, but I don't see in the VBA code anywhere where the table is called, let alone set as a recordset and then used to set date and time variables.

 

[Kevin320]

Time is a Text Format. Why would you use that.

Do you have a problem if you stick to Date and Now. If the answer is no then why use Time.

Just a passing thought as I really don't understand what you are trying to do here.

I have several routines that run throughout the day, and there are different actions that are taken at different times of the day. I simply use Time to determine the current time when the routine is run to see which action to take.

Here is a sample:

Dim AssignBeginTime, AssignEndTime As Date

AssignBeginTime = #9:00:00 AM#
AssignEndTime = #9:30:00 AM#

If AssignBeginTime < Time() And Time() < AssignEndTime Then

 

[Kevin320]

I think there's a problem with VBA's Date/Time library.

Create a brand new database and run the same tests.

I'll try this

 

[vbaInet]

Following-up on something GinaWhipp suggested, I started looking at values in tables within the database. I came across one with the offensive date and time information, but I don't see in the VBA code anywhere where the table is called, let alone set as a recordset and then used to set date and time variables.

And you're certain none of your fields if called Date? Did you also look in your code?

Let me know what the outcome of the second test case is.

 

[GinaWhipp]

What happens to your test if you remove the offensive Date() and Time() from the Table? And, have you run a Find to look for the Table name? Got to be there somewhere if it's being used...

 

[namliam]

What comes to my mind are (global) variables or fields that are called date and time.
Thus instead of calling the actual function it is actually reading the variables that hold the 'improper' date and time because they come from some recordset or something.

 

[Kevin320]

What happens to your test if you remove the offensive Date() and Time() from the Table? And, have you run a Find to look for the Table name? Got to be there somewhere if it's being used...

I tried changing the date in the table, and, as you suggested, the date in VBA changed accordingly. Therefore, although the table was not being called in VBA as a recordset, or anything else, for that matter, Date() and Time() in VBA was pulling it from the table (the fields were named "Date" and "Time" in the table) because the table was the record source for the form. Case solved.