Query Dates, Access - VBA and VB.Net Tutorials, Education and Programming Services

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.


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 *

Search