how to list the next three month in MS Access Report?

Question

Hey i have an Emp table which have

name + visa no + visa exp date

how i can show in the report the current month and the next 3 month

for example

now iam in march i need to list the Emp there visa will exp in march + the next 2 month

which is April and may

trying to play with the expression builder but i didn't find a solution or its not possible to done that ??

Answer 1

I am sure your report will have a Query on which it is based, so you need to create a filter in the Query. For example if your Report has a Record Source the name of the Query, then go to the Query design view.

The SQL should be something like,

SELECT
    EmployeeName,
    EmployeeVisaNo,
    EmployeeVisaExpiry
FROM
    EmployeeTable
WHERE 
    Month(EmployeeVisaExpiry) = Month(Date); 

To get the information for the current month and the next two months, your Query will be.

SELECT
    EmployeeName,
    EmployeeVisaNo,
    EmployeeVisaExpiry
FROM
    EmployeeTable
WHERE 
    EmployeeVisaExpiry BETWEEN Date() AND DateAdd("m", 3, Date()); 

I have created the Query to start only form the Date and 90 Days/3 Months from the date, so do not be surprised if it pulls through some entries in June too. However if you want just the three months, then you can have it as,

SELECT
    EmployeeName,
    EmployeeVisaNo,
    EmployeeVisaExpiry
FROM
    EmployeeTable
WHERE 
    Month(EmployeeVisaExpiry) BETWEEN Month(Date) AND Month(DateAdd("m", 3, Date())) ; 

The above will give you all the data falling in the three month period, if you want it to filter the ones in the current year, just add an AND to the criteria. Something like,

SELECT
    EmployeeName,
    EmployeeVisaNo,
    EmployeeVisaExpiry
FROM
    EmployeeTable
WHERE 
    ((Month(EmployeeVisaExpiry) BETWEEN Month(Date) AND Month(DateAdd("m", 3, Date())))
    AND
    (Year(EmployeeVisaExpiry) = Year(Date()))) ;