Wednesday 21 November 2012

Playing around with Dates

I have not been able to do any post over the past few months, have been a very busy with work. Anyways, I am planning to make sure I do post, does not matter if it is going to be small or big. 

So, what has been keeping me busy then ?? Hmmmm... one issue which has been troubling me for a long time is simple Dates. Well it might sound a bit bizarre, but it got me banging my head on the wall for a few days, until I had figured out some tricks. 

# How can we obtain dates in Access?

In general Dates in VBA are simple to use you have the Date() that returns the current System date, you can also use the Now() that will return the Date and Time. 
Example of usage:  
Date() would return 21/11/2012
Now() would return 21/11/2012 22:42:39 
As simple as they may sound there are some complications. 

# Difference in Value

Date() and Now() even though have the same information, they do not have exactly the same information. In the sense Date() combined with Time() is the value present in Now(). If you used the Immediate window in VBA editor and type 
? Date() = Now()
The answer you would get is False, as Date() is never equal to Now(), the only way they can be equal is when you extract the Date part of the Now function, using the DateValue() method.  
? Date() = DateValue(Now())    'This will return True

# Regional settings

As the VBA compiler looks into the system for its time and zonal location, you are in big trouble if you are not in the United States. If you are using an American standard, the Date Format used is mm/dd/yyyy where as the normal format is dd/mm/yyyy. Unusual in Oracle/SQL servers where the date format is yyyy-mm-dd; since we are not comparing them, we will stick to the topic of Access. Some ways to trick the system are using format function, this is not the ultimate choice as Format can be a bit tricky as they might convert to String. Say for example you give the function the Date - 01/03/2012, in non US format it is 01-March-2012, However there might be a possibility that the system will mean 03-January-2012. 
Some ways of using Format,
Format(Date() , "mm/dd/yyyy")    'This will return 03/01/2012
Format(Date() , "dd-mmm-yyyy")    'This will return 01-Mar-2012
Format(Now() , "mm/dd/yyyy")    'This will return 03/01/2012
You can play around with the Format method but will not always help you, As Format is basically what it makes you see not the real data.

# Solution ?? !!

Does not matter what the format is as along as the data is precise that is all you need. Displaying a date is simple, you can show it as per your need, if you prefer showing mmm-dd-yyyy or dd-mm-yyyy or dd/mmmm-yyyy any Format is achievable through the Format(), but all you need is to store the right information. Did you know all dates can be (actually they are) represented as Decimal numbers in Geek terminology it is a Double Number. What I mean by that is, for example in your immediate window if you type 
? CDbl(Date())
you will get a value (for date 21/11/012) 41234.00, which is a Double representation of the Date, So the numbers before the decimal represent the Date Part and the number followed by the decimal point represent the Time bits. So if you try,
? CDbl(Now())
you will get some value like 41234.96684, so when using a UPDATE or INSERT statement that involves time, to preserve the originality of the Date/Time simply convert them to double values by using the inbuilt function CDbl(). 
UPDATE logTimeTable SET agentID = 24, dateTimeLoggedIn = " & CDbl(Now) & " ;
Once the update is completed if you check the table it will have the data as the same format that you have set in the design view and the Date and Time value will be preserved. The main beauty of using this is because you do not have to use ## when dealing with dates, you do not have to worry about formatting. When dealing with queries, you do not need to throw in a Format to match the one in the table. 

So this is one work around dates, that makes your life with Access a bit ore easier. 

If you have any access related questions the best place to clarify would be Access World Forums, in which I am an active member of (under the name pr2-eugin). Apart from ('not really that great') me, there are also very talented and highly experienced MVP's, who can help you out with your questions.