INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

CODE

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:

CODE

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

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
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.

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close