Date and Time Functions not using current info

[Kevin320]

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.

That is exactly right. Thank you.

 

[vbaInet]

I suspect that variable is there for a reason though. You will need to investigate deeper.

 

[GinaWhipp]

Kevin320,

You need to rename those fields going forward and any other fields that are using Reserved Words. For a complete list of Reserved Words, see...

http://allenbrowne.com/AppIssueBadWord.html

As you have just experienced it does cause problems when not addressed. Using Naming Conventions will help avoid that. For more on that see...

http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.access-diva.com/d1.html

Getting off my soap box now...

 

[RainLover]

An interesting discussion.

One should remember this to demonstrate to others.

 

[GinaWhipp]

I was thinking the same thing!

 

[vbaInet]

That is exactly right. Thank you.

So Kevin what was the exact culprit?

From your test case that failed it looks like you used Date but where you used Date() it worked correctly. And I'm sure if you had used VBA.Date or VBA.Date() they both would have worked.

I think in this scenario it was necessary to include the parentheses (even though it gets discarded in the VBA editor) or use the full reference to the library, VBA.Date().

However, if the culprit was a const variable declaration - which I'm almost certain it is - I would have hoped that the compiler will complain. I didn't expect that you can just overload this method just like that. Even in my VBA editor it won't let me declare Date as a variable let alone a public const. Can anybody else?

 

[GinaWhipp]

@vbaInet,

The issue was the use of the word Date as a field name that was also included in the Record Source. While Access will ignore the fact that you used the word Date as a field name it will only do so if you remember to put brackets around it. Of course, earlier versions would sometimes let it slide depending on how you used it, later versions are not letting anything by.

The compiler will not complain as it one, ignore field names in tables and two, when it see it in code expects you to bracket if you named a table field Date. That said, adopting a naming convention will eliminate this issue altogether...

Side note, if you type Date() in to the VB Editor it will, depending on its use, strip the (), so even that cannot be garanteed. Best recourse is again, for everyone to adpat a naming convention for field names in tables.... IMHO.

 

[vbaInet]

@vbaInet,

The issue was the use of the word Date as a field name that was also included in the Record Source.

Thanks for pointing that out Gina. My empty mind didn't even think along those lines.

The compiler will not complain as it one, ignore field names in tables and two, when it see it in code expects you to bracket if you named a table field Date. That said, adopting a naming convention will eliminate this issue altogether...

Yep, sure it won't. My brain was bent on a constant variable being the culprit and the shock horror of the compiler allowing a function to be overloaded. I simply thinking out loud in my last post. And of course I advocate adopting good naming conventions.

Side note, if you type Date() in to the VB Editor it will, depending on its use, strip the (), so even that cannot be garanteed.

Sure, I did mention that in my post.

 

[GinaWhipp]

@vbaInet

Hmm, need more coffee???

 

[vbaInet]

@vbaInet

Hmm, need more coffee???

Please get me one

 

[Kevin320]

So Kevin what was the exact culprit?

Hi all. I'm sorry, I was offline over the long US holiday weekend, so I'm just getting back to this.

It's as Gina indicated. My new associate named a field "Date" and another field "Time" in the table that was being used as the Record Source for the form. Therefore, vba was looking to that table, and those fields, when we were calling the Date() or Time() functions.

I've had a naming convention discussion with him.

 

[GinaWhipp]

Hmm, perhpas your new associate could benefit from one or more of the links below, just saying...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102