Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB
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?
 
NB:
MS Access
Windows Forms
Visual Basic 2010.
Posted 24-Sep-12 7:53am
Eyo1458
Rate this: bad
good
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
-- =============================================
CREATE FUNCTION [dbo].[CREATEDATE] 
(
	-- Add the parameters for the function here
	@aYear INT = 0,
	@aMonth INT = 0,
	@aDay INT = 0
)
RETURNS DATETIME
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DATETIME
        -- 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)
	BEGIN
		SELECT  @Result = DATEADD(mm,(@aYear-1900)* 12 + @aMonth - 1,0) + (@aDay-1) 
		--another method:
                --SELECT @Result = CONVERT(DATETIME,@aYear + '/' + @aMonth + '/' + @aDay) 
	END
 
	-- Return the result of the function
	RETURN @Result
 
END
 
And finally, the query should looks like this one:
DECLARE @begDate DATETIME
DECLARE @endDate DATETIME
 
--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]
FROM PEOPLE
WHERE [dbo].CREATEDATE(YEAR(GETDATE()), MONTH([Birthdate]), DAY([Birthdate])) BETWEEN @begDate AND @endDate
 
[EDIT]
In MS Access:
SELECT  [pName], [Birthdate]
FROM PEOPLE
WHERE DateSerial(Year(Date()), Month([BirthDate]) ,Day([BirthDate])) BETWEEN Date() and DateAdd("d", 7, Date());
[/EDIT]
 
That's all!
  Permalink  
v3
Comments
Maciej Los at 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 at 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
good
Please Sign up or sign in to vote.

Solution 1

Something like this would be good:
SELECT *
FROM Birthdates
WHERE BirthDate BETWEEN now and DateAdd("d", 7, now);
  Permalink  
Comments
Maciej Los at 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ő at 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 at 25-Sep-12 15:09pm
   
5, because you might be right ;)
Rate this: bad
good
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())
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 290
1 Jochen Arndt 165
2 DamithSL 125
3 PIEBALDconsult 110
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 25 Sep 2012
Copyright © CodeProject, 1999-2014
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