Check if a date lies in a timeframe

[Ben_Entrew]

Hi all,

I wanna check the regarding time frame of given date.

Somehow the comparison doesn't work out. pricedate is a given date selected by the USER.

I tried following:

Public Sub TEST()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim start_date As Date, end_date As Date
Dim a As Double, c As Double
'Grap the right pricing timeframe
Set db = CurrentDb
Set rs = db.OpenRecordset("Pricing_timeframes")
Do While Not rs.EOF

If rs!Valid_from <= pricedate <= rs!Valid_to Then
  
  start_date = rs!Valid_from
  end_date = rs!Valid_to
  
  Debug.Print pricedate
  Debug.Print start_date
  Debug.Print end_date

Exit Sub
Else
End If
rs.MoveNext
Loop
Exit Sub
End Sub

Can anyone tell me how to write this IF statement correctly?

Thanks in advance.

Regards,
Ben

 

[namliam]

If rs!Valid_from <= pricedate and Pricedate <= rs!Valid_to then

Keep also in mind when working with dates, these need to be actual date formats or in US date format... Just because I have no clue where pricedate is comming from.

 

[Ben_Entrew]

Thanks Namliam,

I'm such a fool.

I also found out that this Exit Sub is not so useful, if I want to do other things after the loop.
Can you tell me how I can exit from the Loop?

Thanks.

 

[pr2-eugin]

Add Exit Do, instead of Exit Sub.

 

[Ben_Entrew]

Thanks Paul.
It works now.

Regards,
Ben

 

[pr2-eugin]

Glad to help ! Good Luck