Query Dates, Access

This article will explain how to run queries on dates in an access database.

It will be assumed that our database has the following data:
Sample Data

3 fields with the second field storing dates.


Contents

Selecting the Right Datatype:

It is essential that dates are stored in a field with the “Date” datatype. Storing dates in fields with other data types may result in errors:
Date Datatype


Method 1:

In this example it is assumed we want to get data from all the records with a date value later than 2013:
Query

The following Criteria was used:

[table1].[DateField]>DateSerial(2013,0,0)

The function DateSerial receives as input 3 values. A year, month and day. It returns a date value which can be used to compare against the dates in the database.

Sql Statement:

SELECT Table1.ID, Table1.DateField, Table1.SampleData
FROM Table1
WHERE (([table1].[DateField]>DateSerial(2013,0,0)));

Result:
Result


Method 2:

Similar to the previous example we want to get data from all the records whose data field is later than the year 2013:
Method 2

The Criterion below was used to filter the dates:

[table1].[DateField]>#1/1/2013#

The number signs are automatically added by access. They specify that the value between them is a date.

Sql Statement:

SELECT Table1.ID, Table1.DateField, Table1.SampleData
FROM Table1
WHERE (([table1].[DateField]>#1/1/2013#));

You can download the sample file for this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *