find records that start before a date and end after a date

[wstech]

I have table with three fields the Status, BeginDate, and End date, I would like to find a record that starts on/before the BeginDate and ends on/After FinishDate. i created a module to query the table, when i call the function the txtbox remains empty.

here is what I have So far.

Module1
Option Compare Database
Option Explicit

Public Function UnitStatus(BeginDate As Date, Finishdate As Date)
Dim strStatus As String
Dim tblUnitStatus As String

CurrentDb.OpenRecordset ("Select * From tblunitstatus")
strStatus = "SELECT tblUnitStatus.Status FROM tblUnitStatus WHERE tblUnitStatus.BeginDate <=(DateSerial(Year(Date), 1, 1) AND tblUnitStatus.FinishDate >=(DateSerial(Year(Date), 1, 1)"


End Function

Private Sub Form_Load()


Me.lblDate1.Caption = Format(((DateSerial(Year(Date), 1, 1))), "ddd")
Me.lblDay1.Caption = Format(((DateSerial(Year(Date), 1, 1))), "dd")
Me.TxtStatus1.value = UnitStatus(1 / 1 / 2010, 1 / 1 / 2010)

not sure why it does not work, I have been searching and have not found the answer yet

 

[PHV]

Public Function UnitStatus(BeginDate As Date, Finishdate As Date)
UnitStatus = DLookUp("Status", "tblUnitStatus", "BeginDate<=#" & Format(BeginDate, "yyyy-mm-dd") _
 & "# AND FinishDate<=#" & Format(FinishDate, "yyyy-mm-dd") & "#")
End Function

and then in the form's procedure:

Me!TxtStatus1.Value = UnitStatus(#2010-01-01#, #2010-01-01#)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wstech]

Thanks for the fast response, I amended the code as you suggested, the code is still not passing a value to the text box. when I run the compiler the code does not spike any errors, not sure what to do next.

 

[wstech]

figured it out the sign after FinishDate is transposed, after I changed it to Greater than or Equal to (>=) it worked fine. thank you for the help. I was able to modify it to a date serial based on your suggestions.