473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Declare 'Builtin' Function

675 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim CYear as Integer
  2. 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 24 '15 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Function Year(Date)
  2.     Member of VBA.DateTime
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.

9 2122
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Function Year(Date)
  2.     Member of VBA.DateTime
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.
Jun 25 '15 #2
OldBirdman
675 512MB
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.
Expand|Select|Wrap|Line Numbers
  1.  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.
Jun 25 '15 #3
jforbes
1,107 Expert 1GB
Try Now() instead of Date()
Jun 25 '15 #4
OldBirdman
675 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim ii As Long
  2. Dim CurYear As Integer
  3. Debug.Print Time
  4. For ii = 1 To 10000000
  5. CurYear = VBA.Year(XXX)
  6. Next ii
  7. 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
Jun 26 '15 #5
OldBirdman
675 512MB
Correction: ByRef should be ByVal in post #5
Jun 27 '15 #6
jforbes
1,107 Expert 1GB
I should have clarified. I was curious if:
Expand|Select|Wrap|Line Numbers
  1. CurYear = Year(Now())
would be interpreted correctly.
Jun 28 '15 #7
NeoPa
32,556 Expert Mod 16PB
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.
Jun 30 '15 #8
OldBirdman
675 512MB
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)
Expand|Select|Wrap|Line Numbers
  1.     Dim MyDate As Date
  2.     Dim MyTime As Date
  3.     Dim MyDateTime As Date
  4.     MyDate = "12/4/1977"      'This could be MyDate = VBA.Date
  5.     MyTime = #2:13:37 PM#     'This could be MyTime = VBA.Time
  6.     MyDateTime = "10/2/2007 7:15:44 PM"     'This could be MyDateTime = VBA.Now
  7.             'I did not use system times / dates to make them very different
  8.     Debug.Print "Test1 ", MyDate, MyTime, MyDateTime
  9.     Debug.Print "Test2 ", VBA.Year(MyDate), VBA.Year(MyTime), VBA.Year(MyDateTime)
  10.     Debug.Print "Test3 ", VBA.Hour(MyDate), VBA.Hour(MyTime), VBA.Hour(MyDateTime)
  11.     Debug.Print "Test3A", VBA.DatePart("h", MyDate), VBA.DatePart("h", MyTime), VBA.DatePart("h", MyDateTime)
  12.     Debug.Print
  13.     Debug.Print "Test4 "DateDiff("s", #6/29/2015#, #7/2/2015#)
  14.  
  15. Results:
  16. Test1         12/4/1977     2:13:37 PM    10/2/2007 7:15:44 PM 
  17. Test2          1977          1899          2007 
  18. Test3          0             14            19 
  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
Jul 1 '15 #9
NeoPa
32,556 Expert Mod 16PB
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(MyDateTime) gives you that.
OldBirdman:
There is no easy way to extract the time from MyDateTime
TimeValue(MyDateTime) 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
Jul 2 '15 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Pierre Rouleau | last post by:
Hi all, In several occasions, I found myself looking for a function that would take a value and restrict is within a specified set of boundaries. For a 1-dimension value, I could simply write...
0
by: User | last post by:
I am looking for a function that can dial up an ISP, connect and/or log off through a Python script running under windows. I looked in socket, but didn't find anything that looks promising. Any...
0
by: Qwert | last post by:
Hello, kind of a specific question, but here it goes: The following code works: Dim objGraphicsSource As Graphics = PictureBox1.CreateGraphics Dim bmpSource As New Bitmap(PictureBox1.Width,...
2
by: James Thiele | last post by:
What exceptions (if any) can the python builtin compile() function throw besides SyntaxError?
1
by: yancheng.cheok | last post by:
currently, i have a private function in cat named privateFun. i would like to have this function "private" to all except dog's action member function. by using the following approach, all the...
2
by: mshahzadali | last post by:
is there any expert who could guide me making a C++ program. I want to convert a Decimal number into a Binary, Octal and a Hexadecimal Number using a C++ Built-in Function(if there exist any).
6
by: afraze | last post by:
Hi! My problem is that: When i declare an AnsiString function in my Form.h("Form.h" is an ex. name) i am getting this error: :Unresolved external ' __fastcall System::TObject::ToString()...
2
by: Sreeramu | last post by:
Declare a pointer to a function which takes array of pointer to a function as an argument and return the pointer to a function..... how to declare this....
6
by: grbgooglefan | last post by:
I am creating functions, the return result of which I am using to make decisions in combined expressions. In some expressions, I would like to inverse the return result of function. E.g....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.