Date Function

Returns a Variant ( Date) containing the current system date.

To set the system date, use the Date statement.

If you use the Date function with a Gregorian calendar, the behavior of Date$ is unchanged by the Calendar property setting. If the calendar is Hijri, Date$ returns a 10-character string of the form mm- dd — yyyy, where mm (01-12), dd (01-30) and yyyy (1400-1523) are the Hijri month, day and year. The equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Query examples

SELECT Date() AS Expr1 FROM ProductSales GROUP BY Date();

Returns the current system «date» for in short date format. Result: «dd/mm/yyyy».

SELECT Date()-[DateofSale] AS DaysSinceSale FROM ProductSales;

Returns the difference between current system «Date» and date values of the field «DateofSale» as number and displays the results in column «DaySinceSale».

VBA examples

Use Date in an expression You can use the Date function wherever you can use expressions. For example, you can set the Control Source property of a text box on a form as follows:

When the form is open in Form view, the text box displays the current system date.

Use the Date function in VBA code

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

This example uses the Date function to return the current system date.

How to Enable Last Access Time Stamp To Files on Windows

To save system resources in Vista, Microsoft disabled the Last Access Time Stamp. With Windows 7, this time stamp remains disabled. Windows 7 isn’t the system hog that Vista was. So I think it’s time we turn ‘last access time stamp’ back on. Especially since most of you probably never knew it was disabled in the first place!

So, read on, and we will review how to enable the Date and Time accessed details for all files and folders in Windows 7. This How-To can be a useful trick if you think someone or something (spyware?) other than you might be using your computer, or perhaps you’re a forensics corp IT guy? Anyway, a few things to keep in mind before we get started with this Tutorial:

1) Enabling or Disabling this won’t make your Accessed date vanish or reappear, rather it will only change if Windows updates the timestamp or not.

2) If a last accessed time stamp already exists for your file, it will remain persistent unless this feature is enabled. Once enabled, however, all of your apps will become aware of when they are being used/accessed.

How To Add Access Time Stamps To All Of Your Files In Windows 7

1. Click the Start Menu Orb and then Search for cmd. Once that comes up Right-Click cmd.exe and Select Run as administrator.

2. The UAC prompt will appear… Click Yes!

3. Now in the cmd prompt, Type in “ fsutil behavior set disablelastaccess 0 “ and Press Enter.

4. Restart your PC for changes to take effect.

Bingo! Now Time stamps are enabled, and you’ll be able to see when files are accessed from now on. Take note on really OLD PCs this might cause performance issues, but even that isn’t too common.

Okay, now we’ll look at the other side. Maybe you have Time Stamps enabled, but you are ready to give them the boot.

How To Disable Last Access Time Stamps In Windows 7

Open the cmd prompt from administrator mode like before, but this time Type in “ fsutil behavior set disablelastaccess 1 “

How To Use Last Access Time Stamps In Windows Search Results

Manually looking through the properties of every file on your system could take years. This scenario is where Windows Search comes into use. Using Search + Timestamps you’ll be able to sort by access date and see exactly what went down on your PC, exactly when it all happened, and sometimes even in what order.

1. In the Start Menu search area, Type * and Press Enter.

Searching for a * sign signifies that Windows Search should pull up every single file on your computer. The ‘See more results’ button is another great way to get into the searching mood.

2. Switch the Window view to Details.

3. Right-Click category bar and Click More.

4. On the next window Scroll down and Check the box for Date Accessed and then Press OK.

That’s it! Now when you do Windows Searches, it will pull up and show the Date accessed category and you can organize it better than this picture by just Clicking the Category name.

Groovy Comment or Suggestion? Post it below or join the discussion in the groovyPost community forum!

How to Work with Dates and Times in Microsoft ® Access Databases

Tips on how to ensure that dates are input and stored correctly in an Access database, and then processed as intended in formulas, forms, queries and reports.

Last updated on 2020-05-26 by David Wallis.

What’s the Date

Consider what you understand the date to be when you read 24/09/2019. You and your computer are probably in agreement that 24/09/2019 refers to Tuesday 24th September 2019.

If you’re working in the United States and you’re guessing I’m a Brit writing this piece, you’d assume I meant 09/24/2019.

But what about interpreting 12/09/2019?

In adopting a blueprint for dates, a database developer should have in mind questions like these:

• Is Access’s record of the input date the same date as the user wants recorded?
• Does the recorded date register as the same date with all users of the database?
• Do my queries work correctly if they include date filters or expressions based on dates?
• Will dates in reports produced by the database and sent to people outside the office be understood correctly by the recipients of those reports?

Your Computer’s Region Settings

Much of Access’s response to dates and times, is determined by the region to which the computer you’re using is set. The setting is in Window 10’s Control Panel: open Control Panel and navigate via Clock and Region —

to display the Region dialog box —

The settings shown above are for my computer, as supplied to me in the UK. I don’t alter these default settings, for reasons I explain below. I illustrate them here because they have an influence on the results of some of the tests on Access’s conduct of dates and times that appear below.

As a database developer, I never insist that a client adopts one set of Language Preferences over another. I believe I must make a database manage dates and times correctly irrespective of any tinkering a particular user may do to their Windows settings.

Access’s Dates and Times

Access stores input into a Date/Time field as a floating point number; that is, a number with an integer part and a decimal part. The integer part is the date, the decimal part, the time.

The integer part of a number stored in a Date/Time field relates to Access’s built-in calendar that runs from January 1st 100 CE to December 31st 9999 CE. Access stores January 1st 100 CE as the number -657434; December 31st 9999 CE, as 2958465.

Count one for each day since January 1st 100 CE to the date you have in mind and you have the number Access stores representing that date. Count 43843 and you’d be at January 13th 2020.

The decimal part of a number stored in a Date/Time field represents time as a fraction of 24 hours. .66666 represents 3:59:59 PM — very nearly two-thirds of the way through a day, at 15 hours, 59 minutes and 59 seconds. Add another decimal place — .66666 6— to achieve 4:00:00 PM exactly.

A number with digits to the left and right of the decimal point is how Access stores date and time together. For example, 44790.25 is August 17th 2022 6:00:00 AM.

Exploring Dates and Times in Access

You can use the Immediate Window in Access’s Visual Basic Editor to test how Access works with dates. Copy and paste any of the examples below into your Immediate Window and then press Enter to see the result .

?Format(44790.25,»dd/mm/yyyy hh:nn:ss AM/PM»)

17/08/2022 06:00:00 AM

The Format(date,format) function displays the value assigned to its first argument — in this case the 44790.25 — in the format assigned to its second argument.

The result appears as I’d expect it to because the format I assigned follows the Language Preferences set on my computer.

What if I muck about with the assigned format?

?Format(44790.25,»mm/dd/yyyy hh:nn:ss AM/PM»)

08/17/2022 06:00:00 AM

Access isn’t fussed by a change to the format. Formatting is cosmetic — for appearance purposes only. What matters to Access is that the date is 44790.25.

Here we’ve omitted the seconds and AM/PM components:

Format adjusts to the 24 hour clock as appropriate:

Note that minutes are indicated by “n”s to distinguish them from “m”s for months.

Here are some examples to demonstrate the flexibility of the Format function:

?Format(44790.75,»dddd d mmmm, yyyy»)»>

Wednesday 17 August8, 2022

“dddd” and “mmmm” causes Format to spell out days and months in full; “ddd” and “mmm”, the first three letters only.

Date and Now Functions

Access’s and VBA’s Date() function returns the current date determined by your computer’s clock:

?Format(Date(),»dd/mm/yyyy hh:nn:ss AM/PM»)

14/01/2020 12:00:00 AM

Date() returns a whole number — no decimal places. We learn that Access considers the start of the day to occur at midnight.

The Now() function returns current date and time as determined by your computer’s clock:

?Format(Now(),»dd/mm/yyyy hh:nn:ss AM/PM»))

14/01/2020 04:57:48 PM

Try that example a couple of minutes later:

14/01/2020 04:59:42 PM

DateSerial Function

Access’s and VBA’s DateSerial(year,month,day) function provides a means of constructing a date (that is, the number that Access uses itself to represent a number) out of values assigned individually to the year, the month and the day, in that order. This in the Intermediate Window of my computer:

Access has used the preferences set on my computer to format the result as a short date. We can demonstrate that DateSerial is returning a number, as we would anticipate:

Testing for any time component, we learn DateSerial assumes none:

Dates and Times at Point of Input

If you set the Data Type property of a field in an Access table to Date/Time, then Access will reject your attempt to input into that field anything that it fails to interpret as a date or as a date and time.

If you input into that field a date, or a date and time, in a pattern that doesn’t match that in your Region Settings, then Access will exercise initiatives in interpreting your input.

In my experience, a user will input dates and times in the way in which he is habituated. He may use the Date Picker set against a text box, if the Picker is available; or he may type “24/9/2019”, “24/9”, or “24/9/19” or any other conceivable arrangement of day, month, year and separation characters. Or he may copy and paste.

There are two man issues to address:

• That the user sees the date and time that Access is recording as a result of her input
• That a stored date and time is what the user intends and not a spurious one resulting from Access misunderstanding the input.

Hence the two sections that follow.

On-Screen Display of Dates

Let’s assume that the user is inputting into a text box on a form; and assume that she’s inputting using whatever technique she chooses. We’ll use this snip taken from her Invoice form to illustrate the effects various settings in her form have on the display of the input date:

The lower text box is named “tebInvoiceDate”. tebInvoiceDate has as its Control Source the InvoiceDate field in the table storing details of invoices. The Data Type of the InvoiceDate field is set to Date/Time.

These three properties of tebInvoiceDate have all been left blank: Format, Default Value and Input Mask.

Everything is in harmony: Access has stored the date as January 6th 2020, just as the user intended it to be.

Now, some misguided individual chooses to add their own design touches to the form and assigns mm/dd/yyyy to the Format property of the tebInvoiceDate text box:

The displayed Invoice Date has adopted the mm/dd/yyyy format applied by the vandal to the text box in preference to the default dd/mm/yyyy of the computer’s Regional settings.

😕 Surely this is likely to cause the user confusion …

😡 … and frustration as the user fiddles achieving a date that looks right and in so doing stores one that is wrong.

To avoid any confusion, frustration and mistakes, my approach is either to leave the text box’s Format property blank or to select a format from the list in the Format property’s drop-down list:

I’ll qualify that approach by recommending that the year is always displayed as four, not two, digits. This is a personal preference that comes out of experiences with databases that store dates from years ago or in the future.

For example, the record of an employee in an HR database. His date-of-birth input as “20/2/49” is meant to be February 20th 1949 — not February 20th 2049 as the database has stored it:

An example from another database: the user input what they intended to be November 19th 2008 by typing “19/11/08”. Are you guessing correctly what Access made of this?

The # characters indicate to the Format function that the value it is evaluating is a date.

You could devise a set of rules based on the concerns raised above:

• Don’t fiddle with a computer’s Regional settings in Windows
• Don’t apply customised formats to controls on forms that display dates
• Do if there’s a need to apply date format to a control, pick one from the drop-down list in the control’s Format property. The same applies to controls for the input of time and of date and time
• Do display the year as four digits, never two.

Storage of Dates and Times

In the design of tables, the developer has the option of whether or not to assign a format to any Date/Time field. I’ve experienced no great advantage in applying a format.

What’s more, I’ve received databases for conversion and upgrading in which formats applied to Date/Time fields have caused unnecessary work checking that they did not interfere with the intended running of forms, queries and reports.

Hence my recommendation:

• Don’t apply a format to any Date/Time fields in tables.

Default Date and Time Settings

You can set the Default Value property of a control on a form and of a field in a table.

You may consider it helpful to the user to pre-populate a Date/Time field with today’s date in a new record for, say, the receipt of an order. In such a case use the Date() function as the default.

Apply the Now() function only when you specifically need time recorded. In the section about date and time filters in queries, below, I explain why it’s inadvisable to use Now() when it’s date-only that you want recorded.

Validation Rules

In a Date/Time field in a table, Access stores the date Thursday 23th January 2020 as the number 43853. It stores 6:00 AM on Thursday 23th January 2020 as 43853.25.

One way to guide a user to input a date with no time component is to assign a rule to that fields Validation Rule property. If the field in question is named “DrawdownDate”, then this is one expression you could assign to its Validation Rule:

If a user inputs anything that Access considers has a time component, then Access will prevent the user leaving that field until he has adjusted his input to a date alone, no time.

If the user is to be allowed to leave that field without any data value, then the Validation Rule needs extending:

(([DrawdownDate]-Int([DrawdownDate]))=0) Or IsNull([DrawdownDate])

Adding a message like “Please input only a date, no time” to the field’s Validation Text property will spare the user from incomprehensible messages from Access when he transgresses your validation rule.

Dates in Reports

Weekly Acquisitions Report
For the Week Beginning 20/04/20

dd/mm/yyyy and mm/dd/yyyy have always been popular format properties. OK if your database is used exclusively locally. No so if your database is outputting reports as PDFs that you’re sending to recipients in other parts of the world.

Potentially even more confusing are the dd/mm/yy and mm/dd/yy formats. To what date in a report title attached to an email you receive does “20/04/20” refer, do you assume?

Surely a format that conveys the date as the same to everyone is a sensible approach to adopt: dd mmmm yyyy , dddd dd mmmm yyyy or the ISO 8601 recommendation yyyy-mm-dd , for example.

Weekly Acquisitions Report
For the Week Beginning 2020-04-20

Date Filters in Queries

Consider a query named “qsDeals” which selects deals from a table named “tblDeals”:

This query produces a data sheet listing Deals for the year 2019:

Now let’s apply a filter for Deals made on Wednesday 9th October 2019. Note that to include a date in a filter, the date must be prefixed and suffixed with a # character:

All good because the date in the filter follows the Short-Date Regional setting — dd/mm/yyyy — of the computer in use:

What you, as a developer, have to bear in mind is the result of this query when it’s run on a computer with Short-Date Regional settings of mm/dd/yyyy . You’re going to get Deals for Tuesday 10th September 2019.

To avoid situations like this, you could consider the use of Access’s DateSerial(year,month,day) function. The filter for October 9th 2019 would be DateSerial(2019,10,9) , thus ensuring no uncertainty as to which date is operative, whatever Regional settings are in force.

Expressions in Query Filters for Dates

Used for filtering dates in a query, the DateSerial(year,month,day) function will accept expressions. In this example running the query presents the user with a message asking for the month for which records are to be filtered:

>=DateSerial(2019,[Month number?],1) And =DateSerial([Year?],[Month number?],1) And Warning If you’re filtering on a date field into which both data and time have been input, read the next topic!

Filtering Date and Time

Consider the query qsDeals based on a table into which users have input a mix of dates and dates-and-times into the table’s DrawdownDate field. Here’s the data set correctly filtered for September 1st 2019 (UK):

Now see the result of applying the simple filter #01/09/2019# (UK date format) to the same data:

If this result is one you weren’t expecting, you’re not the only one. I’ve experienced a number of reports meant to present daily information. The reports were totally inaccurate, simply because their developers had failed to make provision for the way in which Access stores dates and therefore how dates should be queried.

Similar failings I’ve be asked to fix occured in reports based on times, e.g. call-logging modules in databases intended (supposedly) to report calls ordered by shift.

Beware Between

At first sight the use of the Between……And…… expression might seem an ideal way of filtering across a range of dates. For example, Between #01/09/2019# And #30/09/2019# to deliver September 2019’s records.

Ask yourself what that filter will return when records contain both date and time. Will it include or exclude September 30th 2019 10:30 AM from the results?

On all occasions, I favour the >=……And…… construction over Between……And…… .

Another caution over “between”: when your receive a request such as “A report, please, on all orders valued between ₤5,000 and ₤10,000”.

Always best to ascertain precisely what is required of the report. Is an order of value ₤5,000.00 to be included? Does a ₤10,000.00 order belong in the requested report or in another for “high-value orders”?

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by Pay Pal in GBP (£ sterling) —

To make a contribution by Pay Pal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Thanks, in anticipation.

“Hi DMW, I am an MS Access Database novice, wishing to develop an application for my small business. I have seen an application that is close to my requirement, but can’t tweak it much due to my limited access skill. If I can afford your services, I would like to enjoy it. Thanks.”