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.
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.
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 Year(Date)
-
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 : - Function Year(Date)
-
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.
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.
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.
Try Now() instead of Date()
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
Correction: ByRef should be ByVal in post #5
I should have clarified. I was curious if:
would be interpreted correctly.
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.
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 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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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,...
|
by: James Thiele |
last post by:
What exceptions (if any) can the python builtin compile() function
throw besides SyntaxError?
|
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...
|
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).
|
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()...
|
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....
|
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....
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |