Need help with a date range query 1

[SoonerNation]

This query works sometimes, I could sure use some help.

Requirement: User provides starting month/year and ending month/year. I need to query for dates in that range.

The Query seems to work fine until ending month is less than starting month. The data set is huge, so I don't know exactly what the results should be. But, anytime ending month is less that starting month, zero records are returned??? i.e. Start= 12/2005 End=01/2006 returns zero records and should be 4,000-5,000.

The query I'm using:
Select rDate,BYR,SSN
From Form100
Where (Month(rDate)>=#Val(Form.StartMonth)# and Year(rDate)>=#StartYear#)
and (Month(rDate)<=#Val(Form.EndMonth)# and Year(rDate)<=#EndYear#)

Thanks
Lyndon

 

[Golom]

Syntax like #EndYear# coerces the text string in EndYear to a date (i.e. number of days since Dec 30, 1899).

Year(rDate) however returns a year number like 2005 or 2006.

Similar comments apply to #Val(Form.EndMonth)# and Month(rDate)

Try something like

Select rDate,BYR,SSN
From Form100
Where rDate BETWEEN 
 DateSerial (StartYear, Val(Form.StartMonth), 1) AND
 DateSerial (EndYear  , Val(Form.EndMonth)+1, 0)

 

[PHV]

A starting point:
SELECT rDate,BYR,SSN
FROM Form100
WHERE (100*Year(rDate)+Month(rDate)) Between (100*[StartYear]+[StartMonth]) And (100*[EndYear]+[EndMonth])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SoonerNation]

Thank you Golom,

Works great. I need to study the DateSerial() function to see what you did.

Lyndon

 

[rac2]

A couple of ways to tackle this.

Build strings with the year and month, for example "200601" and "200512". These strings, or text values, have the correct sort order. Then obtain the year and month values from the rDate and build the corresponding string for that.

WHERE CStr( Year(rDate) & Month(rDate) ) BETWEEN
      StartYear & StartMonth AND EndYear & EndMonth

Or, build dates from the form values, do a little date/time calculation and compare the resulting value to the DATETIME values in the table. First use the form values to build a string representing a valid date on the first of the month, '12/01/2005'. Convert that to a DATETIME value, CDate('12/01/2005'). Next add one month and substract one day from that value to obtain the date of the month-end, DateAdd("d", -1, DateAdd("m", 1, CDate('12/01/2005'))). Use that value in the WHERE condition to compare to the DATETIME values in the table.

WHERE rDate BETWEEN

        DateAdd("d", -1, 
          DateAdd("m", 1,
             CDate( StartMonth & "01" & StartYear )  )   )

        AND

        DateAdd("d", -1, 
          DateAdd("m", 1, 
           CDate( EndMonth & "01" & EndYear )  )   )

Hope this helps. Apologies for any syntax errors I am not very good with Access SQL.

 

[SoonerNation]

OK Golom I read up on DateSerial(),

What an elegant solution! Thanks again.

Beginners, like me, may be interested in this explanation.

Select rDate,BYR,SSN
From Form100
Where rDate BETWEEN 
DateSerial (StartYear, Val(Form.StartMonth), 1) AND
 DateSerial (EndYear  , Val(Form.EndMonth)+1, 0)

DateSerial() returns a date variable constructed from the values provided as in: DateSerial(YYYY,MM,DD) all three parameters must be numeric.

In my query the start date is easy enough. The beauty of this solution is in the end date of the range. When a value that is out of range is passed to DateSerial() the function adds or subtracts and return the resulting date.

Examples:

DateSerial(1977,1,1) returns 1/1/1977
DateSerial(1977,1,0) returns 12/31/1976
DateSerial(1977,1,-1) returns 12/30/1976
DateSerial(1977,-1,1) returns 12/1/1976