Click here to Skip to main content
13,256,123 members (40,749 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Dear Professionals,

I am lost in this problem below:

I have an Access Table that stores Members Info including date of births.
With my Windows Forms Application using VB2010, I want to query the Members Table to select only members whose birth dates fall between NOW and the next 7 days.

That is, Peter's Birth date is: 27rd Sept. 1982. But today's date is 24th Sept. 2012.
In 3 days time, Peter will be celebrating his birth date.

With this in mind, I have created a listbox control to store the names of persons selected from the members' table whose birth date is between NOW and next 7 days.

I only need to retrieve the SELECT statement and I can populate the LIST Box control with the data using a ADODB.RECORDSET object.

I do not know how to complete the SELECT query of this task. Please can someone help me?

MS Access
Windows Forms
Visual Basic 2010.
Posted 24-Sep-12 7:53am
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

If you would like to compare the BirthDate with some range of dates, you need to use a simple trick: replace the year part of [BirthDate] with current year and compare it with these dates.

But how to replace the year part in BirthDate?
In MS SQL Server 2012 use the DATEFROMPARTS[^] method. In the previous versions of MS SQL Server, you need to write custom function to create date from parts of BithDate. For example:
-- =============================================
-- Description:	Gets date from parts
-- =============================================
	-- Add the parameters for the function here
	@aYear INT = 0,
	@aMonth INT = 0,
	@aDay INT = 0
	-- Declare the return variable here
        -- replace '-' date separator with '/' if necessary ;)
	SET @Result = '1900-01-01'
	-- Add the T-SQL statements to compute the return value here
	IF (@aYear>=1900 AND @aMonth>0 AND @aDay>0)
		SELECT  @Result = DATEADD(mm,(@aYear-1900)* 12 + @aMonth - 1,0) + (@aDay-1) 
		--another method:
                --SELECT @Result = CONVERT(DATETIME,@aYear + '/' + @aMonth + '/' + @aDay) 
	-- Return the result of the function
	RETURN @Result

And finally, the query should looks like this one:
--get the current date
SET @begDate = GETDATE()
--get the current date + 7 days
SET @endDate = DATEADD(d, 7, @begDate)
--if you would like to see the result dates, uncomment below command
--SELECT @begDate As [dateFrom], @endDate AS [dateTo]
--trick: replace the year part of [BirthDate] with current year and compare it with above dates
SELECT [pName], [Birthdate]
WHERE [dbo].CREATEDATE(YEAR(GETDATE()), MONTH([Birthdate]), DAY([Birthdate])) BETWEEN @begDate AND @endDate

In MS Access:
SELECT  [pName], [Birthdate]
WHERE DateSerial(Year(Date()), Month([BirthDate]) ,Day([BirthDate])) BETWEEN Date() and DateAdd("d", 7, Date());


That's all!
Maciej Los 25-Sep-12 6:33am
Moved from answer, OP comment:
Dear Maciej,

You have talked about creating a function in MYSQL. I am using MS Access as my backEnd.
So, how would I create the function in MS Access?

Secondly, the CODE for MS Access that you included above as
SELECT [pName], [BirthDate]
FROM TableName
WHERE DateSerial(Year(Date()), Month([BirthDate]), Day([BirthDate])) BETWEEN Date() AND DateAdd("d", 7, Date());

Please what are these variables and what are their functions?
1. DateSerial
2. Date

Thank you.
Maciej Los 25-Sep-12 6:48am
Sorry... The part of solution where i show you how to solve your problem in MS SQL Server is mistake. When i've read your question for the first time, i haeven't see that database is in MS Access. Although the answer is good.

More about above functions you'll find here: Date(), DateSerial().
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Something like this would be good:
FROM Birthdates
WHERE BirthDate BETWEEN now and DateAdd("d", 7, now);
Maciej Los 24-Sep-12 17:09pm
Not exactly... Why? 27. September 1982 is not between TODAY() and TODAY()+7.
See my answer ;)
Zoltán Zörgő 25-Sep-12 2:23am
Might be, but the OP had following requirement: whose birth date is between NOW and next 7 days. But you have right, the question should have been: whose birthday is between NOW and next 7 days...
Maciej Los 25-Sep-12 15:09pm
5, because you might be right ;)
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

In sql server u can try like :
select * from Test where TestDate between GETDATE() and DATEADD(DAY, 7, GETDATE())

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171114.1 | Last Updated 25 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100