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:
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:
Method 1:
In this example it is assumed we want to get data from all the records with a date value later than 2013:
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:
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:
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