Comparing date ranges

[dariley]

I'm trying take a date value that entered through the calendar contro
and use it to determine which pay period that date belongs to. But
even though the calendar1.value is crossing properly, the Select Cas
method is picking the first date range every time. Below is a sampl
of the code I'm using, I'm not sure what other details I need t
include...this is my first time to post. But, any help is VER
appreciated.

Private Sub CmdSubmit_Click()
Dim CalValue as Date
CalValue = Calendar1.Value 'Determine the Pay Period
CalValue = Format(Date, "mm/dd/yyyy")
Select Case CalValue
Case "2/22/2004" To "3/6/2004"
PayPeriod = "2"
Case "3/7/2004" To "3/20/2004"
PayPeriod = "3"
Case "3/21/2004" To "4/3/2004"
PayPeriod = "4"
Case "4/4/2004" To "4/17/2004"
PayPeriod = "5"
Case "4/18/2004" To "5/1/2004"
PayPeriod = "6"
End Selec

 

[Frank Kabel]

Hi
try the following (not fully tested)

Private Sub CmdSubmit_Click()
Dim CalValue as Date
CalValue = Calendar1.Value 'Determine the Pay Period
Select Case CalValue
Case CDate("2/22/2004") To CDate("3/6/2004")
PayPeriod = "2"
Case CDate("3/7/2004") To CDate(""3/20/2004")
PayPeriod = "3"
Case CDate(""3/21/2004") To CDate(""4/3/2004")
PayPeriod = "4"
Case CDate(""4/4/2004") To CDate(""4/17/2004")
PayPeriod = "5"
Case CDate(""4/18/2004") To CDate(""5/1/2004")
PayPeriod = "6"
End Select

 

[dariley]

Thanks, but had the same result. CalDate equaled 3/30/2004, and i
still picked the first argument in the Select Case making PayPeriod
"2". It's frustrating, I've been scratching my head over this one fo
several days

 

[Frank Kabel]

Hi
the following works for me. I have used my regional settings date
format ("DD.MM.YYYY"). You have to change the date format according to
your regional settings


Dim CalValue As Long

CalValue = CDbl(Calendar1.Value) 'Determine the Pay Period
Select Case CalValue
Case CDbl(CDate("22.2.2004")) To CDbl(CDate("6.3.2004"))
payperiod = "2"
Case CDbl(CDate("7.3.2004")) To CDbl(CDate("20.3.2004"))
payperiod = "3"
Case CDbl(CDate("21.3.2004")) To CDbl(CDate("3.4.2004"))
payperiod = "4"
Case CDbl(CDate("4.4.2004")) To CDbl(CDate("17.4.2004"))
payperiod = "5"
Case CDbl(CDate("18.4.2004")) To CDbl(CDate("1.5.2004"))
payperiod = "6"
End Select