CODEWITHSUNDEEP
vb.net
Diamond
Diamond

Reputation: 608

How to get month within a range vb2010

I just don't know how to go about this.

I designed a program that uses MS Access as its database. I have a field for month and year (the field data type is text) where user can register details. The program will register the month and year the user have chosen e.g month= September, year=2011.

My problem now is how to chose a range of data to view by the user using the month and year as a criteria e.g the User may want to view data range from (September 2011 to July 2013).

I couldn't figure out even how to try. Help will be highly appreciated.

Upvotes: 0

Views: 538

Answers (4)

Kym NT
Kym NT

Reputation: 790

In this question are some ways to do this:

First. Filter the dates in a range assuming that you use a date like '07-12-2012'

i.e. September 2011 to July 2013

 Where DateColumn > '09-01-2011' and DateColumn < '07-31-2013'

OR

Specify a Date and a Year

 Where month(DateColumn)='1' and year(DateColumn)='2016'

Note: There are many ways to do this.

You can Manipulate your statement depending on your desired output.

Upvotes: 0

the_lotus
the_lotus

Reputation: 12748

You can assume that everything is entered on the first day of each month. I would pull the information using a query to the database.

select * from [tablename] where DateSerial([colYear], [colMonth], 1) between DateSerial([fromYear], [fromMonth], 1) and DateSerial([toYear], [toMonth], 1)

Upvotes: 1

Reporter
Reporter

Reputation: 3948

A range is from a startpoint until an end point. For the startpoint you can add automatically the first of Month. For the endpoint is it more complicated because there is no fix endpoint. What you can do is following:

  1. Write an array that contains for each month the days (e.g. 30 or 31). Except for Febrauary there is a fix pattern.
  2. for Febrauary use the selected year to check is the year a leap year or not. If not add 28, else add 29.
  3. After that create the date string for your SQL: Startdate 1.9.2011. Do for the entdate the same.
  4. After that, I think you can use the keyword between in your SQL query.

Upvotes: 1

Sean Airey
Sean Airey

Reputation: 6372

Perhaps you could change your application logic to store the month and year as their respective numbers rather than text and change the field data types to numeric.

You could then construct a DateTime object from them, for example September would be 9 and you could use code like the following:

var startDate = new DateTime(year, month, 1); // get year and month as integers from database, uses the first as the date
var endDate = new DateTime(year, month, 10); // change the date but keeps the month and year the same
var endDate2 = startDate.AddMonths(1); // adds 1 month to the date

Alternatively, you could try using a calendar control to allow the user to select two dates instead of building it from a number of fields. Depending on what you are using this could be achieved a number of ways, for example in ASP.Net or WPF you could use two calendar controls and just use their SelectedDate properties as your range.

Upvotes: 1

Related Questions