how to do date comparison between a list of different dates?

[lemagnifique]

Hi everyone,



i have a pb. here's the situation: using an access database i tried to code the following in vb



i have a table of customers orders (for each order, i have order date, starting date and ending date)



i want to check how many orders exists in a particular year (from 1/1/2007 until 12/31/2007) so between i have to check starting date and ending date. after that i want to know how many exists (count) in jan 1/1/07, feb 2/1/07, etc... (note the table has records from 2005 and 2006 too which have orders for 2007).



for eg, in 2005 for march 05, there are 10 orders with ending date in 2007 (3 in jan 07, 5 in april 07, 2 in dec 07) so when doing the procedure i want it to return those 10 orders as result 3 in jan07, 5 in april 07, 2 in dec 07. + those in 2006 and 2007.



for testing purpose i created a form and tried only on one month to see if it will check it but for some reasons it stops and does not go through.



here's the code



Private Sub Command4_Click()

Jan.Value = 0
Feb.Value = 0



Dim DIN As Variant
Dim Cal As Variant


Dim YearMonthStart As Variant
Dim YearMonthEnd As Variant



YearMonthStart = "12/1/2007"
YearMonthEnd = "12/31/2007"



Yearstart2.Value = YearMonthStart
Yearend2.Value = YearMonthEnd



DIN = DateIn.Value



Do
If DIN >= Yearstart2.Value And DIN <= Yearend2.Value Then
MsgBox "true"
Exit Do
Else
Yearstart2.Value = DateAdd("m", "-1", Yearstart2.Value)
Yearend2.Value = DateAdd("m", "-1", Yearend2.Value)

MsgBox "noooo"

End If
MsgBox "outside if"
Loop Until Yearend2.Value > "12/31/2006"



Cal = DateAdd("m", "-1", DIN)
'MsgBox Cal

Result.Value = Cal



End Sub



Any ideas? any help would be much appreciated.

thanks,

Lemagnifique.