Declare 'Builtin' Function

[OldBirdman]

I used the name "Year" in tables as a field name. Now I would like to use the Access function Year(Date) within a user function.
The code:

Dim CYear as Integer
CYear = Year(Date)

gives me a Run-time error '13'; Type mismatch, because Year is a field name.
Is there a way to declare 'Year' as a Microsoft Function within a subroutine, i.e.

Dim Year as Function

If not, is there an easy way to change all references to a table field name within my project? Includes Tables, Forms, Queries, and VBA Code.

[NeoPa]

Hello old friend :-)

I actually doubt that Year() is being confused for the Field reference at this point. What happens when you place the cursor within that word and press Shift-F2 to see the definition. If that takes you to the Object Browser and shows something like :

Function [B]Year[/B]([I]Date[/I])
    Member of [U]VBA[/U].[U]DateTime[/U]

then you know that isn't the issue.

If it turns out that it is then you can specify it as VBA.Year() to be explicit. Otherwise I'd be looking at the value passed in your code as Date. That's a reserved word and could mean the Date() function or anything else you've declared, if indeed you even have.

[OldBirdman]

Thank you NeoPa for the quick reply.

Shift-F2 get me the message:
"Cannot jump to 'year()' because it is in the library 'C/Users/..../Books.accdb' which is not currently referenced."
That message is meaningless to me.
Books.accdb is of course my Project/DataBase name
Pressing 'Help' on the MessageBox takes me to a page about Visual Studio 2012 which wants me to subscribe before I can determine what it is, but I can 'Get Started for Free', which couldn't display the page.

 CYear = VBA.Year(Date)

does what I want, to force the name "Year" to be the function which extracts the year from a date variable.
The name "Date" was intended to be the current/system date, but Date() is changed to Date by the VBA Editor.

[jforbes]

Try Now() instead of Date()

[OldBirdman]

Why?
Using VBA.Year forces the compiler (intepreter) to operate the way I want, so problem solved.
The Now() function is the Time&Date as a single number. With the intepreter, there would be some (miniscule) time savings to zero the right-most digits. If compiled, a good optimizing compiler should make the statements equivalent, by passing the argument requiring the least amount of work for the function. As the argument in this case is passed ByRef, there is no need to not pass what is needed rather than what is coded.
I ran the following code:

Dim ii As Long
Dim CurYear As Integer
Debug.Print Time
For ii = 1 To 10000000
CurYear = VBA.Year(XXX)
Next ii
Debug.Print Time

in interpreter mode (not compiled) replacing XXX with Date and Now respectively. To the nearest second, each took 4 seconds. Running the loop for only 1,000,000 times took 1 second, so I presume that the call was actually performed, and not somehow optimized out of the For...Next Loop.
Thank you for your interest in my problem

[OldBirdman]

Correction: ByRef should be ByVal in post #5

[jforbes]

I should have clarified. I was curious if:

CurYear = Year(Now())

would be interpreted correctly.

[NeoPa]

OldBirdman:
"Cannot jump to 'year()' because it is in the library 'C/Users/..../Books.accdb' which is not currently referenced."

That indicates to me that you probably have a Year() procedure of some form defined in your database. Strangely though, I would expect if defined in the normal way for it to have taken you straight to the code where it's defined rather than failing.

As you say though, that's simple curiosity. VBA.Year() answers the fundamental question for you.

OldBirdman:
The name "Date" was intended to be the current/system date, but Date() is changed to Date by the VBA Editor.

That's always seemed strange and confusing to me too. It's a function reference so why are the parentheses stripped? Just something to live with I've decided.

[OldBirdman]

Bytes

My original question has been answered. VBA.Year works for me, at least until I find the conflict.

Access is inconsistant defining values that are dates. In Tables, table elements are Date/Time. In VBA, the Dim statement requires the keyword Date. In either case, the data is stored as Long, with the integer part the days since a starting date (1=Jan 1, 1900), and the fractional part the fraction of the day (0=Midnight, 0.5=Noon, 0.75=6:00:00PM or 18:00:00, and so forth). There are enough fractional places to express an accuracy to at least 200ms.
The VBA Now() function returns the current system date & time. Both parts of the Date/Time field exist. The Date() function returns a Date/Time with the time as 0 (midnight). The Time() function return a Date/Time with the date set to 0 (12/31/1899 as 0 and negative dates count back from 1/1/1900)

    Dim MyDate As Date
    Dim MyTime As Date
    Dim MyDateTime As Date
    MyDate = "12/4/1977"      'This could be MyDate = VBA.Date
    MyTime = #2:13:37 PM#     'This could be MyTime = VBA.Time
    MyDateTime = "10/2/2007 7:15:44 PM"     'This could be MyDateTime = VBA.Now
            'I did not use system times / dates to make them very different
    Debug.Print "Test1 ", MyDate, MyTime, MyDateTime
    Debug.Print "Test2 ", VBA.Year(MyDate), VBA.Year(MyTime), VBA.Year(MyDateTime)
    Debug.Print "Test3 ", VBA.Hour(MyDate), VBA.Hour(MyTime), VBA.Hour(MyDateTime)
    Debug.Print "Test3A", VBA.DatePart("h", MyDate), VBA.DatePart("h", MyTime), VBA.DatePart("h", MyDateTime)
    Debug.Print
    Debug.Print "Test4 "DateDiff("s", #6/29/2015#, #7/2/2015#)

Results:
Test1         12/4/1977     2:13:37 PM    10/2/2007 7:15:44 PM 
Test2          1977          1899          2007 
Test3          0             14            19 
Test3A         0             14            19

Notes:
Test3 and Test3A are identical
There is no easy way to extract the date from MyDateTime
There is no easy way to extract the time from MyDateTime
There is no easy way to assemble MyDateTime from MyDate and MyTime
Notes++:
While mucking around with this, I note that VBA ignores Leap Seconds, as occurred 30 June 2015 @24:59:60 UTC, but that won't bother me. DateDif

[NeoPa]

Let's see what light I can shed on this.

OldBirdman:
Access is inconsistant defining values that are dates. In Tables, table elements are Date/Time. In VBA, the Dim statement requires the keyword Date.

All true, but the VBA Date type for variables is actually a Date/Time variable.

OldBirdman:
In either case, the data is stored as Long, with the integer part the days since a starting date (1=Jan 1, 1900), and the fractional part the fraction of the day (0=Midnight, 0.5=Noon, 0.75=6:00:00PM or 18:00:00, and so forth).

Not quite. They are both stored as Doubles (Double-precision floating point) and the starting point (Equivalent to a date of 0.) is 12/30/1899. Strange I know, as it isn't the last day of that century as would be expected, but there you are. The time parts are all exactly as you say.

OldBirdman:
The Time() function return a Date/Time with the date set to 0 (12/31/1899 as 0 and negative dates count back from 1/1/1900)

As earlier, this is out by one day but I have no idea why they chose that as a base.

OldBirdman:
There is no easy way to extract the date from MyDateTime

DateValue(MyDat eTime) gives you that.

OldBirdman:
There is no easy way to extract the time from MyDateTime

TimeValue(MyDat eTime) gives you that.

OldBirdman:
There is no easy way to assemble MyDateTime from MyDate and MyTime

MyDate + MyTime gives you that. Another trick, that I picked up only recently, and furthermore is limited to whole seconds, is :
DateAdd("s", DateDiff("s", 0, MyTime), MyDate)

OldBirdman:
While mucking around with this, I note that VBA ignores Leap Seconds, as occurred 30 June 2015 @24:59:60 UTC, but that won't bother me.

I expect you're right. I've never even considered that one :-D