Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do you find a range of dates?

    I want to look for a range of dates in a table.

    For example, 8/12/2003 through 5/15/2004 (don't limit the date to only that)
    There is only one field in the table with the "Date".

    The days shouldn't matter, just the month and the year. I have try the "Between" function but it doesn't work the way I wanted it to be because it is pulling the 2004 to for the August month.

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Lansing,

    How are you using the between function, could you post the SQL?

    I've never had problems with:

    Code:
    SELECT * FROM table1 t1 
    WHERE t1.datemod BETWEEN #8/12/2003# AND #5/15/2004#
    Let me know...

    -Warren

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    Quote Originally Posted by WarrenM
    Lansing,

    How are you using the between function, could you post the SQL?

    I've never had problems with:

    Code:
    SELECT * FROM table1 t1 
    WHERE t1.datemod BETWEEN #8/12/2003# AND #5/15/2004#
    Let me know...

    -Warren
    Oops! I didn't clarify it clearly.

    I created a form that has the combo box for the month and the year. I use a query to get the data from the table. The table has a field named, "Date From and Date To." I use both of the field. From there, I query out the data with the criteria that the user have choosen on the form. I'm just not querying the whole date rather I'm just querying part of the date like the month and the year not worrying about the days.

    Here is a sample of the query without the other field in it:

    SELECT DatePart("m",[Date From]) AS MonthFrom, DatePart("yyyy",[Date From]) AS YearFrom, DatePart("m",[Date To]) AS MonthTo, DatePart("yyyy",[Date To]) AS YearTo

    FROM mwtrn

    GROUP BY DatePart("m",[Date From]), DatePart("yyyy",[Date From]), DatePart("m",[Date To]), DatePart("yyyy",[Date To])

    HAVING (((DatePart("m",[Date From])) Between [Forms]![frmCharts]![cboMonthFrom] And [Forms]![frmCharts]![cboMonthTo]) AND ((DatePart("yyyy",[Date From])) Between [Forms]![frmCharts]![cboYearFrom] And [Forms]![frmCharts]![cboYearTo]) AND ((DatePart("m",[Date To])) Between [Forms]![frmCharts]![cboMonthFrom] And [Forms]![frmCharts]![cboMonthTo]) AND ((DatePart("yyyy",[Date To])) Between [Forms]![frmCharts]![cboYearFrom] And [Forms]![frmCharts]![cboYearTo]));

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Wow I'm confused...

    I don't think I get what you're trying to do?

    Let me try to clarify...

    You have a table with two attributes/columns: 'date_to' and 'date_from'.

    From that table you want to grab the rows in which the 'date_to' and 'date_from' fall within the user criteria entered into your form?

    Now, if those criteria start within the date range but do no end within the end date range do you count it, and vise versa?

    Let me know if I'm off...

    -Warren

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    I'm query on both of the field. I will not be counting any but pulling out the data. The field will show the full date like: Date From: 8/12/2004 Date To: 8/25/2004. The Date From and Date To are field names of the table.

    There is a form that I created that has the combo box for the month and the year. I don't need to query it out with the days, only the month and the year. So I have to do something like [Forms]![frmCharts]![cboMonth] and vice versa for the year to be put in the criteria for the query. I have try this way and it didn't work.

    I was hoping there would be another way of doing it or I'm doing something wrong. I that clarify it.

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    lansing,

    So couldn't you just write a query like this:

    Code:
    SELECT the_columns_you_want from the_tables_included
    WHERE form.datevalue >= date_from AND form.datevalue <= date_to
    -Warren

  7. #7
    Join Date
    Sep 2004
    Posts
    161
    It's normal,
    if i suppose a record with Date_to 8/12/2003 and Date_to 8/31/2004
    a [cboMonthFrom]=8 [cboYearFrom]=2003
    and a [cboMonthTo]=5 [cboYearTo]=2004
    with the syntax used, the test is
    Month Date_From=8 Between 8, 5 Ok
    Year Date_From=2003 Between 2003,2004 Ok
    Month Date_To=8 Between 8,5 Ok !!!!!!!
    Year Date_To=2004 Between 2003,2004 Ok
    I think the test must be
    Month_Year Date_from=8/2003 Between [cboMonthFrom] [cboYearFrom] ,
    [cboMonthTo] [cboYearTo] Ok
    Month_Year Date_to=8/2004 Between [cboMonthFrom] [cboYearFrom] [cboMonthTo] [cboYearTo] Nok

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    Quote Originally Posted by WarrenM
    lansing,

    So couldn't you just write a query like this:

    Code:
    SELECT the_columns_you_want from the_tables_included
    WHERE form.datevalue >= date_from AND form.datevalue <= date_to
    -Warren
    thanks, I would never thought of it that way. Greatly apprepicated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •